Help with a calendar design

[Hans_Clausdorff]

Hi everyone!. Hope can someone help with a design that I’m struggling with for a long time.
I’m in charge of a database a medical speciality program (Emergency Medicine).

I have a base with the list of persons (residents) and a list of rotations where the residents needs to rotate every month for the whole duration of his/her program duration. For example, one month, he/she needs to rotate in pediatrics, the next one in ER, the next in the ICU and so on.

The problem is, I’m trying to join both tables (residents and rotations) with all the conditionals that needs to exist and I’m having troubles in terms of design and visualization:

1. I’ve created the junction table (Rotations Calendar) where I can see the month in the primary field and the rotations in the other fields, linking to the residentes list. So I can see who is rotating in each month, how many residents are rotating in some rotations (counting the linking records) and in the residents table I can see when the resident is rotating in each rotation.

2. In order to not repeating residents in certain rotations, I’ve created several views that shows me residents who don’t have a month assigned. However I have trouble with a specific rotation, where the resident rotates 16 times. I don’t know how to create a view that shows me the residentes available for this specific rotation and don’t have another rotation previously assigned.

3. Does Anyone knows an app that improves the visualization of this?. In stacker works pretty (filtered by resident) fine but in airtable it’s kind of a mess.

Hope you can help me with this task!

[Kamille_Parks]

I’m making a custom app that I think might be useful to you? I posted a demo of it here:

Selectively auto-creating records in a Junction Table Base design

Also I did go ahead and add the feature to select records in a grid view and use that selection within my app. Here’s a video of how it works so far:

So it seems like you’d be able to select the “Rotations” table, then select a record in the “Rotations Calendar” table (the current month’s record, probably), then as you add a “Resident” you’d see whether you’re about to create a duplicate junction record.