Help

Re: Urgent help needed with repetition formula

1880 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Tiago_Melo
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi All,

Is there an available formula where I can see if I have a repetition of an item in the same field(column)?

Thanks,
T

16 Replies 16

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

Tiago_Melo
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

Tiago_Melo
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

Tiago_Melo
5 - Automation Enthusiast
5 - Automation Enthusiast

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.