Urgent help needed with repetition formula

I think you’re trying to find duplicates, maybe you can read other threads: https://community.airtable.com/search?q=duplicates

Hi Elias,

I have various different values in one cell and I want to know if any of those same values exist on another cell on the same field. Not a duplicate though.

Best,
T

Multiple values in one cell? I think you’re going to try and explain better what you have.

I think he has a multivalue field, like MultiSelect or LinkedRecord. I see it as a hard task (but maybe a hack-ish method exist), what are you trying to achieve? Maybe there is a better way.

Basically is a film equipment booking base, for every shoot there’s various kit that gets booked and I wanted to flag if the same kit gets booked for the same date.

Take 5 or 10 minutes to better explain what you’re talking about.

Sure, I work in a production company and we have various amounts of camera and lighting kit. That kit gets booked for location shoots almost on a daily basis, all the Kit is up on airtable as single items.
The main fields are the Kit, date, and project code.
In the kit column each cell is filled out with various codes that correspond to kit items.
The date column is the date of the shoot, it’s very common to have various shoots in the same date.
The project code is unique.
What I want is for airtable to flag when the exact same items of kit are booked in the same day so then when can find an alternative.
Hope this explains everything better.
Cheers,
T

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:

This certainly sounds like an interesting problem to solve, but I’m not able to put time toward data modeling experiments for the next month. Sorry.

Hi Bill,

Thanks for your help and interest on this.

We have various users booking kit and I wanted it to be flagged when someone books the same kit for the same day so we can prep alternate kit.

Yep, I get it.

Formulas work on records one at a time. To solve this you need a formulas that works on a collection of records which does not exist in Airtable.

As such, you need a roll up view that identifies these overlapping commitments. This seems to suggest a relational mapping that exposes paired conflicting records.

I think the solution is a rollup view, not a 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 of any one of a range of dates inside a rollup of ranges of dates – 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.

Yep - this is what I guessed would be needed. This is an excellent success pattern.

To be clear though, I don’t believe there is a Kits table, hence my belief that he needed to re-evaluate the design model to position the solution for this rollup pattern.

Possibly… I think it can work with just whatever his table is that holds the records he is linking to in that “Kit” field. So if those come from a tabled called “Gear” and each record there is a “Piece of Gear”, then just sub those descriptions in wherever I say “Kits” and it should still be able to work the same. As long as each record being checked against the Query has direct access to the “Shooting Dates” it has been booked for.

(it appears I thought I had read his field name as “Kits” plural when I wrote my response, when in fact it is “Kit” singular)

Hi Both,

Thank you so much for all your efforts and help in trying to solve my problem, I will implement what Jeremy suggested next week and will give an update on the process.
Have a great weekend!