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?
Solved
Counting Assignments Between Dates
+3Best answer by Paul_Warren
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
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!
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.
+3Hi 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
+21Hi 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.
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
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!
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.