Re: Urgent help needed with repetition formula
I had to solve a similar issue for a Campground booking system – clearly, it’s poor form for a Campground to book multiple people into the same Campground Spot on the same day.
Catching this issue after the fact (ie, flagging an offending booking after it has been created) proved to be a very difficult way to handle it in Airtable, and would have required a rather hefty Zapier operation. So I opted for a preemptive approach. The preemptive approach I came up with requires another table and another step to introduce into your workflow.
This extra table is used for Querying, and has a single record to which every Kit is linked (ie, whenever you create a new Kit record, you need to link it to the Query record). It has a Date field into which you can manually enter the Shooting Date you want to Query against.
In your “Kits” table, create a Lookup field that pulls the Shooting Date you want to Query against into the “Kits” table.
In your “Kits” table, you will also need a Rollup field that Rolls up all of the “Shooting Dates” for all of the “Projects” a “Kit” is Linked to. If desired, you could also alter the Rollup Formula to eliminate historical dates to keep this field from accumulating cruft data.
Next, in your “Kits” table, create a Formula field that checks for the presence of your Query Shooting Date in your Rollup Shooting Dates. In my case with the Bookings, I had to write an extremely large formula to create date ranges, so I could check for the presence o... – since you seems to be dealing with single, discrete dates, your formula can likely be much simpler, perhaps using a FIND() to check the date as a string against your Rollup field converted to a string. This Formula field should return some value that you can filter on – return “True” if the Query Date is found in the Kit’s Booked Shooting Dates, and “False” if not.
Now you can create a view in your “Kits” table that filters to show only records where the Formula Field above returns false. Let’s say you named that field “Booked?”, so your filter is “Records where Booked? = ‘False’”. Let’s say you name that view “Available Kits”.
Next step - Set your “Kit” linked record field to filter to show only records in the “Available Kits” view. Here’s what my Campground Bookings table, “Site” linked record field looked like:
Your workflow – Whenever you create a new “Location Shoot” record, before you add any “Kits” in the “Kits” linked record field, go to your Query Table and enter the Date for this new Location Shoot. Here’s what my Campground Query Table looked like:
There is only one record there, that links back to every campground spot (in your case, to every kit). You never add new records there, you only update the “Date” field to reflect the Location Shooting Date you are wanting to reserve Kits for. This, in turn, filters your “Available Kits” view to show only Kits where there is not a conflict of dates – where the Query Date is not found in the list of Dates that Kit is already booked for.
After you’ve entered your Query Date, go back to your “Location Shoot” record – when you go to add “Kits” in the Linked field now, you should only see Kits that are available to be used without double-booking in the list of “Kits”, since it is filtered to allow only Kits from the “Available Kits” view:
There are only 12 records available for me to choose from here, even though there are over 300 Site records in total.
