Scheduling Humans with Variable Hours

[Nathan_Renner-1]

Hello!

I am working on a schedule/calendar with a lot of events and data. I would love to be able to use Airtable to schedule humans for different events. I started by using using a format where each event is a record with start/end times, location, and a linked record field to my contact database. However, the issue I ran into is that often humans don’t have the same hours as the event. For example, I might have party with 6 people working it. Some people can be there the whole time, sometimes they have conflicts, etc.

Any brilliant ideas about how to link people to events, when their hours don’t always align perfectly?

I’ve tried creating a third table for individual’s hours. So each record on this table has a linked field to the contact and to the schedule event. It also has an option to update hours which are displayed in a formula on the record name which combines the contacts name and the hours, so that in the schedule people are listed with their hours. I even created an automation on the schedule which allows me to select a contact name from the linked table, and then the the automation creates a new record on the people’s hours table and links that to the schedule and the contact. The problem is that the automation is a little slow and so I can only do one person at time. And if I copy and paste a list, it works but creates only one new record with all the contacts linked. So I can’t edit the hours of just one individual.

[Andy_Lin1]

The solution we used for scheduling writers for auto shows was to have three tables, pretty much as you had set up: Event, Staff, and Attendance.

A time-saving trick for me in data-entry is to group by whatever element is shared between records. So say I’m entering in a bunch of people for a single event, I’ll group by events, and so every new record in a group will be assigned the same event.

Another time-saver: say the same team is working two separate events, I’d group by events, fill in the staffing linked record field for one event, create a blank record in the other event, and then copy and paste the data over. It should ask if you want to expand the group, say yes. If it creates a single new entry with all the names combined, then what’s happened is that Airtable thought you were “editing” the cell, rather than just “selecting” it.

Alternatively, you can change your automation to a script that runs on demand, so you could add the contacts you want and then click the button to add all the scheduling records. (I’m not sure how your current automation is set up, but this option does require you to write code.)

[Nathan_Renner-1]

Oooh very cool! I’d love to see more of how you’ve set it up. I think I’m following, that those are both great timesavers! But I’d love to see what you’ve done

[Andy_Lin1]

Haha, here’s what the Attendance table looks like (I’ve hidden a few fields for clarity):

I have a few more tabs (not only are people working at the show, but they also have stories to deliver), but the important ones are Attendance (=Schedule), Author(=Staff), Show (=Events).

As for my tips from earlier: I have my records grouped by Show, so each new record created in that group will automatically have the Show field filled in (in this case, with LAAS 2018). If I create a record in a different group, I’ll be able to copy the Author names from this group and paste it into that blank record and Airtable will create new entries to accommodate the pasted data.

As for changing the automation to a script, here’s an example from a different base:

When I click on “Create Assignment(s)”, it creates a new article entry (different base, but the equivalent would be an entry in Attendance/Schedule) for every entry linked under “Who’s Going” with the start and end dates (and since they’re copied into regular date/time fields, they’re independent from this event record). Functionally, it’s exactly the same as an automation, but since I control when the script runs, I don’t have to worry about the automation “catching” only the first person I enter, and having to wait however long it takes for the next run.

Mind you, the reason why I don’t have a similar button for the Auto Show table is that I would still need to change details in each of the additional records, so overall, I’d only be saving time entering people’s name into the Attendance table, and that’s… the same amount of time I’m spending entering them into the Event table. The only benefit is the direct link between the Staff and Event table, so you can click on the name of someone working an event and get taken directly to, say, their contact info, as opposed to first hopping through their schedule entry.