Formula to look for scheduling conflict

[VFMFKatie]

Hey Airtable! 

I'm looking for some complex scheduling help. I'm using Airtable to schedule a multi stage music festival, and we're hoping to have a formula that will look for scheduling conflicts for individual performers at specific times. 

I have an example of how we have the scheduling started attached. The main record name is the act, and then we've created different set times for different stages across the three days. There are also individual performers listed for each set, since some performers will be playing in multiple bands. So basically, I want the formula to be looking for scheduling conflicts for individual performers during specific windows of time. 

Is this possible? Do I need to be building the worlds longest IF/THEN formula? 

Appreciate any advice!

[pressGO_design]

Usually, when people think that they’re going to need the world’s longest IF statement, that’s a pretty clear sign that the data needs to be structured differently. In your case, I think you need to have a table, not a single-select field, for Venues. Then your existing Events table turns into a one-to-one junction table for your Performers and Venues so that there’s a separate record for every performer at every venue at every time.

Once you have that, you can do multiple conditional lookups in the Performers table for Event records and use those lookups to create OR filter views that show you where performers are double-booked. 

[Andy_Lin1]

I've never tried this script, but I think you should be able to adapt it to your needs.
https://community.airtable.com/t5/show-tell/scripting-block-detect-scheduling-conflicts/td-p/61659

You likely won't be able to sort it out just a formula due to a couple of issues:

• Airtable formulas aren't aware of record order, so you can't just compare a record to the "previous record"
• Airtable's array functions are pretty limited, so even if you had a sorted rollup, you couldn't run a calculation between start and end times.

[pressGO_design]

Was thinking about this today (why? no idea) and did a quick base mockup that solves this with an automation.

It starts with a form submit for the schedule and then uses a formula to create a Check Conflict field that concatenates the Artist with a string of numbers that represents the start and end date of the event scheduled. The submit triggers an automation that looks for records with the same value in the Check Conflict field and, if it finds any applicable records, puts a checkmark in a checkbox field to indicate that there's a conflict.

Link to base here.

Edited to add correct form link.