Creating a equation to calculate sales for an entire sales team, with different managers and teams

Knack Sales Team App Build

Criteria: I need my knack app to track sales for each individual agent as well as each team. The team is the part I am having difficulty setting up.

These are facts to consider:

There is one main Team, that will be the Matt Agency account/user.
Then I will have agents who report directly to me, direct reports.
These direct reports will have their own personal sales and some of them will have agents who report directly to them. So each agent is essentially a team.

Here is an example of how this will work:

x Agent/Team Matt Agency
xx Bob
xxx Mike
xxxx Jane
xxx Joe
xxx Charlie
xx Jimmy
xxx Suzie
xxx Barb
xxxx Huey

So in this above example any sale Matt Agency makes will be counted as his personal production, his team production would consist of all sales made by Bob, Mike, Jane, Joe, Charlie, Jimmy, Suzie, Barb and Huey

Bob would have his own personal sales and then his team sales would be his sales plus any sales Mike, Jane, Joe or Charlie make

Mike would have his own personal sales then his team sales would be his sales plus Janes sales.

I have created an object called Assignments - each assignment object will set who connects to who and would look like this

  1. MGR Matt Agency / Agent Bob
  2. MGR Bob / Agent Mike
  3. MGR Mike / Agent Jane
  4. MGR Bob / Agent Joe
  5. MGR Bob / Agent Charlie
  6. MGR Matt McCray / Agent Jimmy
  7. MGR Jimmy / Agent Suzie
  8. MGR Jimmy / Agent Barb
  9. Mgr Barb / Agent Huey

This above scenario represents only 4 total levels, but these levels could be as high as 20

Here are the 3 fields i need calculated:

● Personal Sales (easy, this is a simple sum field)
● Direct Report Sales (easy, this is also a simple sum field, totaling all personal sales of the direct reporting agents)
● Team Sales (this is where i need help, it seems like it would be a simple formula, but i can not get it to include sales from agents on the same team that are not a direct report, the ones who have multiple managers in between)

Nic at Knack helped me build this 6 years ago on an app, i am trying to rebuild the exact same app, just cant get past this scenario, knack support doesnt really help anymore like they used to.

I am also ok if equations and formulas eliminate the Direct Report field and only show Personal Sales and Team Sales ----- But if it is necessary for the equations to properly calculate its fine to keep the Direct Report Sales field in there

I have been working on this for a week now plus hired some help on upwork and we can not figure it out, I am looking to the knack community for assistance. Thank you!

Hi Matt, that’s quite a lot to unpack and answer as a forum question. You may be better off engaging the services of a willing Knack Expert: