Oct 22, 2022 01:10 PM
We schedule people to work at many locations. Each location has a unique target number of employees for that day, as well as subtargets for specific roles. For example, a location may need a supervisor, assistant supervisor, three generalists, a specialist 1, and a specialist 2.
We created an employee table and a locations table. The employee table has a position field. The employee table links to the locations table to create the work assignment.
The problem is that you can end up assigning two supervisors or no supervisors, or too many or not enough of any position.
We could create role target fields in the locations table and then count the number of employees assigned to that role in that location, but it’s a whole lot of work. I can’t think of anything more elegant or streamlined.
Ideas welcomed!
Oct 22, 2022 02:29 PM
It sounds like you need to setup a many-to-many relationship, which requires 3 tables: employees, locations, and positions.
Each record in your positions table would have 2 linked record fields: the first linked record field would link to ONE unique employee, and the second linked record field would link to ONE unique position.
You can learn more about many-to-many relationships here:
Oct 24, 2022 10:41 PM
Hmm I think if I were you I’d have three tables:
Employees
remains mostly the same, except with the addition of one view per position type, i.e. each view woud only display employees that match a specific position
Locations
would now contain the information of how many of each positions the location needs, e.g. Location 1 needs one supervisor, one generalist, Location 2
needs one supervisor, one assistant supervisor, two generalists etc
Schedule
would have a linked field to the Locations
table, and multiple linked fields to the Employees
table, one per position type. Each of these linked fields would have the “Limit record selection to view” option checked, and would have the specific view selected. With reference to the example you gave above, I’d have the following linked fields to the Employees
table, each limited to a specific view that would only display employees that were of that position
I would then create a Count
field per linked field above
In the Schedule
table, I would finally create lookup fields to display how many of each positions the location needs, and a formula field that would help me check whether I’ve met position requirements for that location
The final workflow would then be:
Schedule
table, you select the location you’re creating a schedule forGeneralist
Generalist
linked field, you add an employee to it. The linked record selection only displays employees that have the position Generalist
etc etc