My base has two [relevant] tables - “Events”, and “Employees”. Each Event has a date/s, and a Linked Record field to choose one or more Employees to assign to an Event [and a bunch of other non-relevant data to do with the event].
Each employee in the Employee's list can have only one of 5 possible statuses when linked to the Event:
0 - No Response (Employee hasn’t yet responded)
1 - Confirmed (they are assigned to the Event)
2 - Available
3 - Tentative
4 - Unavailable
So my problem is how best to store this data in Airtable. Currently I have it organised so that there are 4 separate columns in the Airtable Base - labelled “Employees Confirmed”, “Employees Available”, “Employees Tentative”, “Employees Unavailable”. This necessitates me adding the employee’s name to the appropriate column and removing it from all of the others. This feels a bit finicky and not scalable (namely, via an external app which I am building), and I just feel like there would be a better way of storing each Employee’s status for each Event. The biggest problem is that it is possible to end up with an Employee in more than one of my current columns (e.g. if Airtable decides the API calls have reached a threshold and misses a “remove” instruction from my external app).
Does anyone here have any insight on how best to structure this and what the key terms I would be looking for (e.g. is there a standard programming term for a second-layer-status-of-a-linked-record? Because that would help with the research).
An alternative idea would be my current structure, but to somehow implement some automation or rule that limits each Employee to show up only in one column at a time. But it would have to be a system where I (or my App) is able to add an Employee to a column and the "rule" automatically removes it from all of the others. If an "exclusive" rule were applied (i.e. "disallow adding to other columns if it is already in one") then I would have the opposite problem with the API calls - the remove-from-all-others call would have to happen first, then the add-to-the-new-status-column instruction could potentially be missed, meaning the Employee doesn't show up in any column!
