Checking within a lookup field for datetime_diff

[anne]

Hi all,

I’ll describe the base and then what I’m trying to do and hopefully someone can help…

On the main table in my base, each row is a person record who has RSVP’d to our event. One of the columns is a lookup column that links to a linking table (clumsy, I know) and returns all the times that that person has been signed up for an event (an appointments table) as a member of a team (yet another table). E.g. I, Anne, a member of Team 1 and Team 2, am scheduled for 1:00PM, 2:00PM, and 3:00PM.

What I want is a way to check within that field to see if there is someone who is scheduled too tightly. Right now, I have this formula: IF(ARRAYUNIQUE(Appointments)!= COUNT(Appointments),"","❌ Problem detected ❌") to test is anyone is double booked with identical start times.

I’d like to also use something like DATETIME_DIFF to check if any start times are within 15 minutes of each other. That’s not an option for rollup (which makes sense to me, given that there could be any number of values in a field - though maybe start/end times are a frequent enough thing that they will implement something similar in the future) and I don’t think there’s a way to do something like IF(DATETIME_DIFF(ARRAYUNIQUE(Appointments),"minutes") < 15,"❌ Problem detected ❌","") - can anyone think of any workarounds?

[Justin_Barrett]

anne:

What I want is a way to check within that field to see if there is someone who is scheduled too tightly.

How would you define “too tightly”? Is that the 15-minute starting time difference that you later said you want to check for, or something else?

On the 15-minute issue, I can’t think of a way to check for that because each registrant could be booked for a different number of appointments. Airtable formulas don’t currently support looping through an arbitrary number of items.

[anne]

Yep, trying to alert any time that someone has been scheduled to start two different meetings within 15 minutes of each other. So your answer is what I was afraid of - darn, I was hoping someone might have found a workaround for something like that.