Marking duplicate or repeat instances

[Fergus_Brown]

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

[Justin_Barrett · Accepted solution]

Welcome to the community, @Fergus_Brown! :grinning_face_with_big_eyes: 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?

[Fergus_Brown]

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!

[Justin_Barrett]

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.

[Product]

Hey Justin,

That's a commendable workaround.

I'm facing a similar problem. 

Context: I'm working in a recruitment environment, where I handle 1000s of candidate's records. In some instance, I might have to reach out to the same candidate for a different opportunity, whose interview status previously might be "Rejected". And since we use standard email templates for email communications, it's becoming a bad experience to candidates, if they're been rejected for a 2nd time & recieving the same templated email message again.

So, in order to rephrase the email message for the repeat candidate, I must have to be aware about the candidate's previous activities ( like {previouslyReachedOutOn:} , {previousInterviewStatus:}, {etc...} is stored in different fields) with us. 

Any help here with the scripting or a formula is greatly appreciated :))

Thanks in advance!!!