Scheduling for a Multi-day event

[Deb_Beardsley]

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

[TheTimeSavingCo]

Hmm, would you say that the main thing you're trying to accomplish is:
1. Let people set the dates they're unavailable
2. Once they're confirmed for a role, automatically assign them as that role for all the dates you guys are active, except for the dates they set as unavailable?

If so, I think you may need a script for this I'm afraid

[Stephen_Orr1]

Hi @Deb_Beardsley,

This might actually be doable with Airtable automations with some minor tweaks to your data structure. Could you please allow your shared base to be copied so I can take a stab at it? 🙂

Thanks,

-Stephen

[Deb_Beardsley]

https://airtable.com/shrv13bFF8qaJjJRj

@Stephen_Orr1 I updated it! Thank you!

@TheTimeSavingCo My husband is a software dev and willing to help me with building something but doesn't know Airtable specifically. Is there a guide or resource that devs would understand that would explain about creating/implementing scripts in Airtable?

[Stephen_Orr1]

Thanks, taking a look. Couple of questions:

How do you know which date to assign in the Scheduler table?

You could use a junction table between scheduler and roles to remove the need for 100 role assignment fields. This would a table of combo day/role records (with 2 links to their respective tables) grouped by day and with one linked record to Source of Truth for day/role assignments.

[TheTimeSavingCo]

Yeap, here's a link to the scripting documentation, and there are also a bunch of scripting examples that you can access by adding the scripting extension to your base.  Nothing too complicated really, anyone with passing familiarity with JavaScript will have no problem with scripting for Airtable