To create an algorithm to accommodate a process like this, we need to get on the same page concerning the vernacular. At the outset, allow me to state a few obvious things that will surely lead to clarification from @Tiago_Melo.
- A “Location Shoot” is planned work at a given location on a given date.
- A “Project Code” is a unique identifier for a “Location Shoot”.
- Each location shoot requires one or more “Kits”
- A “Kit” is a collection of “Items”, ergo - a Location Shoot may require one or more Kits each containing one or more Items.
- Items are uniquely identified by an “Item Code”.
- Items belong to a specific Kit based on their Item Code inclusions in any Kit.
Assuming these are accurate definitions of the data, we can move on to rules.
- Items may float from Kit to Kit depending on production schedules and the shoot requirements.
- Any given Item may exist in only one Kit on any given day.
- Users must not be able to book any given Kit more than once per day, ergo, by extension, Items cannot be booked more than once per day.
Assuming these are the rules (and there are no others), it’s time to move into the data modeling phase.
I’m not an expert when it comes to relational modeling in Airtable, but your data model seems woefully inadequate to handle the rules and relationships stated above.
I defer to some real Airtable data modeling brainiacs like @Justin_Barrett, and @JonathanBowen to lend a hand, but here are my basic observations.
- There should be a master table of Items - it’s basically the inventory you have to work with. It is finite but likely ebbs and flows as your equipment are replaced or lost.
- There should be a master table of Kits that each link to the Items in the Kit. After all, Kits are a function of Item collections.
- There should be a production management table of [unique] Projects that identify the date, location, and Kit(s) needed. This may also include a booking parity field simply red or green based on a clean booking of Kits and their Item collections.
Note that nowhere in this data model do I suggest that Items should be manually listed for any given location shoot. Items are a function of a Kit, therefore, the system need only list the Kit(s) needed for the shoot.
As you can see, we’re building to a point where we have all the parts needed to make it possible for us to understand if over-booking has occurred. What isn’t so obvious is where or how one might apply the perfect formula for flagging overbooking of any specific Item.
It may not be possible to use a formula to achieve this despite the suggested data model changes. My hunch is it will likely be a special View with some intricate filtering or perhaps an entirely different table that lists booking issues.
In my view, a better approach is to design a system that avoids the need to flag for overbooking altogether which may be difficult - Airtable has some missing features needed for real-time evaluations but maybe someone has a clever trick for instantiating a flag field with a green or red dot.
Sorry I don’t have a simple solution for ya, but hopefully this requirements narrative will nudge a good idea onto the stack. :winking_face:
