Hi! Hoping for some insight! I hope I explain this correctly.
I work with a talent staffing company for performers. We staff for seasonal events at large attractions, like a big Halloween event that happens only on select days in October. We hire over 100 performers for these events to fill 100 different roles at the attraction.
I am trying to find a way to generate a daily schedule based on a person's role assignment and availability conflicts. Here's what I have so far:
Table 1 Source of Truth: All performers with their record information (personal info, audition information, hiring/onboarding status, etc).
Table 2 Scheduler: A list of all of the event dates with a link to Table 1 so performers can select the days they aren't available via a form on Table 1.
Table 3 Roles: A list of all 100 roles that are linked to Table 1 so the casting directors can cast the roles upon hiring and info about each role (what area of the attraction they're in, what rehearsal group they're in, etc)
Table 4 Conflicts: A list of all of all of the event dates that is linked to a form in Table 1 so auditioners can indicate the days they're unavailable.
Ideally, I'm trying to write an automation that says: When Role is assigned on Table 1 (via linked column to Table 3), update a date record in Table 2 to reflect who is assigned to the Role. WITH a condition that the person can't be added to the Role if they selected themselves unavailable on a date.
I've found that I can make an automation that When Roles on Table 1 is updated, update a specific typed out record ID date on Table 2 with the Table 1 name in the corresponding role column. But doing it this way doesn't allow the condition to cross reference the Conflicts from Table 4. And I'd also have to replicate this for every date (21 dates) AND every role (100+ roles) which seems ridiculous. I know I'm missing something here.
Side note, during this I realized that my scheduler (Table 2) and conflicts (Table 4) should be the same table but I've built some other automations and form integrations based on this setup during my hands on education building this so I haven't been able to fix it.
Here's a redacted version of my base: https://airtable.com/shrv13bFF8qaJjJRj
