Help

Counting Assignments Between Dates

Topic Labels: Formulas
Solved
Jump to Solution
1566 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Mike_Laufer
5 - Automation Enthusiast
5 - Automation Enthusiast

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?

1 Solution

Accepted Solutions
Paul_Warren
8 - Airtable Astronomer
8 - Airtable Astronomer

@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!

See Solution in Thread

4 Replies 4
Paul_Warren
8 - Airtable Astronomer
8 - Airtable Astronomer

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.

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

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
8 - Airtable Astronomer
8 - Airtable Astronomer

@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!