Skip to main content

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!

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:


Hmm I think if I were you I’d have three tables:



  1. Employees

  2. Locations

  3. Schedule


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



  • Supervisor

  • Assistant Supervisor

  • Generalist

  • Specialist 1

  • Specialist 2


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:



  1. In the Schedule table, you select the location you’re creating a schedule for

  2. You look at the formula field to see what positions you need filled, e.g. it tells you you need one Generalist

  3. In the Generalist linked field, you add an employee to it. The linked record selection only displays employees that have the position Generalist

  4. The formula field updates and tells you you need two assistant supervisors


etc etc

Reply


Terms & Conditions