Scheduling employees for multiple roles by target

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