Link Member Records for Grouping for One Team Report

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • In Progress

My "Teams" table contains multiple "Team Member" records for each Account.  Accounts cross many Teams and have multiple Members & Managers.  I can easily report on all Accounts showing all Members across all Teams (70,000 records).  But I would like to be able to give each Manager a report that shows all Accounts that they have a Member on, but also include the members from the other Teams/Managers.  Filtering by Manager obviously does not work, as it removes the team members that don't report to them. 

My Team's Table is a child of the "Accounts" Table & the "Sales Staff" Table.

I feel like there is an obvious way to set this up, but I can't come up with it.   Any help would be appreciated.

Photo of Nikki

Nikki

  • 284 Points 250 badge 2x thumb
  • Brain dead

Posted 1 year ago

  • 0
  • 1
If you can make a formula checkbox field on the child table called [This member reports to Current User] with a formula something like

User() = [manager userid]

Then you ca make a summary count on a relationship of the of the [# of members who report to the current user].

Then you can filter a single report on that being >0 and send to all managers.
Photo of Nikki

Nikki

  • 284 Points 250 badge 2x thumb

Thanks for the response.   I am not following.   Here is an example of how I have the Teams table laid out.  I want to be able to send Ryan (Manager) a report that includes all members on the Account Team.  Won't summarizing by the manager only show the records of their team members?


Let's get there step by step.

Can you make a formula field which will identify if the Team member reports to the Current user?  Does your app have a User field type for the Team Member's manager?

Assume that the answer is yes.

So you make this formula and it will calculate to true if the current user is the Team member's manager.


User() = [manager userid]

Then you summarize how many team members report to the current user - ie how many team members on that team involve "me" the current user.

Then the Team knows if it is a team that has members that reports to me.

Then lookup that Summary field back down to the Team Members child table.

Now the Team members know if they are on a team where any of my fellow Team members report to the current user, so you can run a report with that filer and not on see your own Team member who report to me, but also any of their cohorts on that team.  The table reports could even color code the Team member as to if they report to me or not.

The feature to "test as user' will be useful for testing purposes here.