Automation Deleting Duplicate Link Records

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?

[
    {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: