Skip to main content

Hi Everyone!:



I’m struggling with an automation that I need for my job (I’m a EM Faculty in South America)…



We have a problem that where we have an activity or something nobody can go because of the shift work. So my boss asked me to coordinate that.


I started to link the four shift schedules (depending on wich hospital each faculty works) that repeats every 6 weeks (That schedule is linked to every faculty member)


After that, I created another table with the meetings and other stuff that we need to know who can not go (it’s a calendar view linked to the shifts. That way I know who is on shift or after a night shif for that event).


The thing is I don’t know how I can make airtable to show me the faculty that actually CAN go to the meeting (who’s not on a shift work).


Does anyone know a solution? Maybe I’m doing something wrong?



Do you have a different table, or just a different view for each hospital. If it’s different tables it will be much more difficult to compare. If it’s different views they you may be able to pull in the shift time. I also may include something like a “Shift type” as a way off shortcutting specific hours and rather saying it’s “morning shift” or “evening shift”… I tend to use that for volunteer management when I need to know who’s available and not scheduled already.

I have the shift type and all the shifts in the same table (4 hospitals)



I’ve worked on this problem a few times but not in the context of Airtable, so my contribution could be worth little to zero value. With such a low expectation, let me press on with a few observations.



#1 When, Not Who



This problem is not about who; it is only about when, specifically and hopefully about lots of when’s. Don’t get distracted by existing schedule details for each individual. Do not get mired in the minutia of every person’s details. Instead, focus on occupied times regardless of who may overlap other people’s occupied time slots.



#2 Clearly State the Requirement





Build an algorithm, that given every member’s meeting schedule, will merge all intersecting meeting times together, making it easier to see when everyone is free ( and by free we mean not in a meeting).





#3 The Inputs



The inputs required for this solution are incredibly simple - an array of objects containing all original meeting times. This could easily be a roll-up table for any given day for all meetings regardless of the person scheduled, right?



r


    {startTime: 0,  endTime: 1},


    {startTime: 3,  endTime: 5},


    {startTime: 4,  endTime: 8},


    {startTime: 10, endTime: 12},


    {startTime: 9,  endTime: 10},


]


#4 Overlapping “Merged” Meetings



Two meetings A and B are considered intersecting if B’s starting time is earlier or equal to A’s ending time. That sounds simple enough but be cautious - codifying this is a bit more difficult in practice. It requires precision handling of dates and times computations which Airtable is totally suited to handle.



#5 The Outputs



An array of objects containing all meeting times merged.



<


    {startTime: 0, endTime: 1},


    {startTime: 3, endTime: 8},


    {startTime: 9, endTime: 12},


]


#6 Rendering the Opposite



The final step is to render the gaps; a list of every hour that does not fall into the occupied time slots. This could be done as as a view designed to look like a calendar of 24 hourly time slots with a filter that removes any slot that falls within the merged times.



Go build that in Airtable. :winking_face:

Reply


Terms & Conditions