Marking duplicate or repeat instances

Thanks in advance for any help,
I am new to Airtable and to using formulas at all. Having trouble figuring this one out, but I’m sure it’s a simple solution.

Context:
I grow mushrooms, 3 different strains (K, B, L). Every day I prepare a new batch (8 bags, same strain). Every week I prepare 5 batches, 1 batch per day, mon–fri.
I need to create a unique record for each batch. Currently, I am using a concatenate formula to combine the strain code (K, B, or L) with the week number it was created. i.e. K.40

My Problem:
2 days of the week, I will repeat the same batch (strain type) I did earlier in the week, which leaves me with two instances of the same thing (K.40).
I need a formula or combination of formulas that can identify when the same batch code has appeared twice in one week, and then assign only the second instance of that occurrence with a (.2) i.e. K.40.2.

See screenshots below for reference:
Screen Shot 2020-10-05 at 17.25.41|700x451

Any help is appreciated,
Thanks a lot

Welcome to the community, @Fergus_Brown! :smiley: Finding repeated items across records is very tough to do with native Airtable features. It’s doable (and I even wrote up a description of how to do it), but setting it up to operate on a weekly basis would be even more challenging, and I don’t really recommend it.

What I would recommend is adding some other unique identifier to the code. Another of Airtable’s functions is WEEKDAY(), which returns a numeric value representing the day of the week; e.g. 0 for Sunday, 1 for Monday, etc. The following formula would add that to the end of your current code:

{Strain Code} & "." & {Week #} & "." & WEEKDAY({Creation Date})

That would create codes like these, using your Week 40 stats as an example:

K.40.1
B.40.2
L.40.3
K.40.4
B.40.5

Would that work?

2 Likes

Thanks for taking the time Justin, appreciate it mate.

It’s a pity there’s no easy way to achieve what I’m after, but using the Weekday formula will certainly be a good workaround, great idea.

Thanks!

To offer a little more background, here’s why there isn’t an easier way to do this. Airtable’s stores data in a database, with each row representing a single record. Database records have no inherent knowledge of other records, so there’s no way to tell a record, “Look for other records in this table that have the same info in a certain field.” Something like that could be done via scripting—either in the Scripting app, or in an automation—because scripts have access to all records. However, the Scripting app option would require you to manually run the script to update the record labels, and the automation option would require you to very carefully establish some trigger activity that would run at the appropriate time. If you’re interested in exploring either of those options, just give a holler. My gut feeling is that you wanted a relatively simple solution, though, which is why I suggested the WEEKDAY() addition to your formula.

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.