Counting Assignments Between Dates

[Mike_Laufer]

I have a table of activities with start and end date columns and a column for the person assigned to the activity. There will be hundreds of activities but only one person should be assigned to each activity. How can I see if a person is assigned to more than one activity during the same time period?

[Paul_Warren]

Hi Mike!

Could you provide some more details on how your base is set up? Do you have separate tables for people and activities? How are they related? Any other details about the base setup will help.

[Mike_Laufer]

Hi Paul,

I do have separate tables. The tables and columns within each table are

Engineers: Engr. Name, Link to table “Activities”

Customers: Cust. Name, Link to table “Activities”

Activities: Activity Name, Link to one Cust. Name, Link to one Engr. Name, Start Date, End Date

There are other columns in each table that have additional data, but they don’t interact in this scenario.

Over time, there will be lots of activities (past, present, and future), each with one engineer assigned. I need to make sure that each engineer is not assigned to more than one activity on the same day.

Best Regards,

Mike

[Justin_Barrett]

The only way I can see this happening is via scripting. Airtable’s formulas aren’t robust enough to do the kind of time-range overlap comparison needed to see if an engineer is overbooked. The JavaScript-based scripting block API is much better suited for this task. You could have two scripts: one that can check for existing overlap, and another one to use when assigning engineers to new activities, which would check for overlap in advance and let you know if any conflict is found.

[Paul_Warren · Accepted solution]

@Mike_Laufer,

Sorry I never responded! For some reason I never got a notification you replied. I agree with Justin (This is like a 6th grader telling his friend he agrees with the teacher, haha). Several scripts have been written to help with things like this. You can find them here: https://builtonair.com/airtable-scripts/

No-Conflict Scheduler: https://builtonair.com/script/no-conflict-appointment-scheduler/?get_back=script-list&is_search=1

ASSET RESERVATIONS: NO DOUBLE-BOOKING: https://builtonair.com/script/asset-reservations-no-double-booking/?get_back=script-list&is_search=1

DETECT SCHEDULING CONFLICTS: https://builtonair.com/script/detect-scheduling-conflicts/?get_back=script-list&is_search=1

I hope one of these can help!