Scripting Block: Detect Scheduling Conflicts

[Jeremy_Oglesby]

This script was written to be used with the “Event planning” Airtable template, and can be adapted to any similarly structured base where the requirement to detect overlapping start and end times is pressing.

When scheduling large Programs with many individual events at different locations, and across multiple days, it can be difficult to keep tabs on whether or not people have been double booked, and scheduling conflicts can cause chaos.

This script is designed to help with that problem. It will check all events in the “Schedule” table for instances where an individual Speaker from the “Speaker & attendees” table has been scheduled to be in two places at the same time!

For each speaker that has conflicting events scheduled, a table will be displayed under their name with a list of any offending events, so that they can be found and rescheduled.

Any table, view, and field names that can vary from one base to another are stored in a constant at the beginning of the script – change the names stored in those constant values to adjust to your specific base.

For example, in Airtable’s Event planning Template, the name of the field that holds a Speaker’s “Event” linked records is "Speaking at" – so the constant value speakerEventsLinkedField is set to "Speaking at". To utilize this in a base with a similar structure, but a different naming schema, just change that line to read:

speakerEventsLinkedField: "Whatever your field name is",

The Script is available in the Scripting Block of this extended version of Airtable’s “Event planning” template, available on the Airtable Universe:

Airtable

Event planning Template - with added Schedule Conflict Script Block -...

This base extends Airtable's "Event planning" template with a Script Block that can be adapted to any similarly structured base where the requirement to...

The script can be manually added to a ScriptIng Block in the following Airtable Template base, or a base structured similarly (with event-like records containing a “Start” and “End” date/time, and person-like records linked to those events):

Airtable

Event Planning Template - Budget, Checklist & Timeline | Airtable

Try Airtable’s user-friendly event planning template to easily keep track of and organize event details, timeline, and budget for a variety of event types

And the code for the script is available here:

gist.github.com

https://gist.github.com/jmoglesby/68f692e09be7bcce857e95b4770ddeec

airtable_schedule_conflict_script

/*
Script: Check for Speaker Schedule Conflicts
Author: Jeremy Oglesby
License: MIT
 
This script was written to be used with the "Event planning"
Airtable template, and can be adapted to any similarly 
structured base where the requirement to detect overlapping
start and end times is pressing.

This file has been truncated. show original

[Sacha_Lochet]

Thanks so much for this script ! Works like a charm :stuck_out_tongue: Do you have any idea on how i could also check location conflicts with this script ?
I’m actually in an improvisation school and really would like to check this kind of thing:
I send Marcus to a school from 5pm to 7pm in one city.
I also asked Marcus to do an other coaching from 7pm to 9pm in an other city.
No schedule conflict here but still impossible for Marcus to be on time on both places.

Thanks for your time, your script, and your help.
Have a nice day,
Sacha Lochet from Improvise!

[Karlstens]

I sat down this evening to play about with flagging date region (as in events that last a few days) clash detection using groups and rollup fields. I got overly excited when my solution worked perfectly for 2 events in one group, but then realised quickly that things fall apart with 3 or more events added to the same group.

Unless I’m mistaken, I think this problem can’t be solved without the aid of a script that checks the dates and flags problems - appreciate you sharing this. Will be interesting to see how it behaves either as a manual check or an automated check after date change.

Thanks again for sharing.

EDIT: Keen to know if anyone has figured out some kind of Rollup formula black-magic that allows us to make date region clash checks without the need of a script - but suspect it might not be possible.

[Aaron_Lowndes]

I'm getting an error using this script, it gives me: 

ERROR
TypeError: Cannot read properties of null (reading 'map')
    at <anonymous> on line 71
    at main on line 69

the only structure variation I can find from your example is that the "Name" field is actually a formula in my table, i.e. CONCATENATE({First Name}," ",{Last Name}). Do you think this would cause the error?

[Aaron_Lowndes]

found the error. You need to account for Speakers who do not have any Events linked (yet, presumably).

Line 71 should be replaced with a ternary operator check for "NULL" from the previous step: 

const eventIds = speakerLinkedEvents ? speakerLinkedEvents.map(event => event.id) : '';