Here’s a link to the base I built this in.
Feel free to copy it into your workspace and poke around.
So there are probably a few ways to go about achieving that.
No matter what you choose, you’ll want to create a table for your sales team like this:
Once you do that, you can either choose to link them to a business day, or you can link them to an individual transaction.
If you choose to link them to a business day record, then you’ll have more flexibility over things like staffing, but you lose a few reporting metrics.
Here you can see that the sales team records are linked to the business days.
You can now create a lookup in the transactions table that will show every team member working that day.
Here’s the limitation:
You can’t easily identify and associate individual transactions with sales team members.
This isn’t a problem if sales performance isn’t attached to a given team member.
Going with the method does allow you to say:
I have an employee assigned to these days, and I want to see the historical team sales performance of the days that they worked.
So if I take the performance metrics from the business days, I can create a rollup that breaks down that data to the days they’ve been assigned. This is probably a significant advantage in looking at macro-trends in how your team is assigned.
e.g. Team A performs 62% better than Team B. I can switch people between teams in order to balance their performance since this team member has a high historical trend etc.
If you decide that you want to link team members to individual transactions, you get a new suite of advantages.
You can now break down how many lifetime sales someone does, the average amount of all their sales, or look at the average amount of all of their sales.
Same as the previous example, you can do a lot with it.
Again, let me know if you have any questions or want to go into something a bit more.
There is a third option that actually allows you to have everything linked, unlike the two methods I displayed.
It would allow you to link a transaction to a team member and business day whilst also having the team member record link to the business day record.
There’s a bit of nuance that goes into it.
It’s not the most technically involved thing, but it’s more of whether you understand how it all works and how the relationships are drawn.
When you start creating many-to-many relationships that are layered on other many-to-many relationships, you can quickly build data bomb that wrecks your single source of truth.
If you know exactly how your base is built, then you’ll be able to keep a leash on it and maintain clean data.
If you want, I can walk you through how to implement it. Just beware of the potential complications.