Need a formula to identify records that have a duplicate value in a field

[Mouser-IB-Admin]

I have a base with a large table, nearly 100k records, which contains a field that we'll call "Invoice #" which should ideally be unique. Over time records have been added which use the same invoice number, and this happens through form submissions as well, so what I'm looking for is the best way to immediately and automatically flag those records as duplicates.

What I'm hoping for is a formula that can do this, populating a value of "Duplicate" or "Unique" depending on what it finds, which I can then use to trigger an automation email alert if a record ever gets updated to show "Duplicate" in that field.

Unique ID	Invoice #	Duplicate? (Formula)
1	12345	Duplicate
2	67890	Unique
3	12345	Duplicate

The frustrating part is that I can group by the "Invoice #" field right now to try and quickly identify the duplicates, but there's no functionality within Airtable to allow me to say I only want to see groups with more than 1 record, or anything else related to the grouping. So I'd still have to scroll through the whole list and look for any groups with more than 1 entry, which isn't feasible with 100k records.

Here's hoping someone out there has had to do something similar in the past and has a functional solution. The de-dupe extension is nice, but it's manual and that's not really helpful.

[ScottWorld]

There are a few different ways of doing this, but I demonstrate one way to easily identify duplicates on this Airtable podcast episode.

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

[Mouser-IB-Admin]

Thanks for the link, will be happy to check it out but do you perhaps have a target area in the video that I can jump to? I don't have an hour of free time today to listen to the whole thing unfortunately.

[ScottWorld]

My segment starts at 23:42

[Mouser-IB-Admin]

Thanks, that was useful info but doesn't seem like it'll help in this case. The field in question doesn't make sense to use as a linked record on a separate table, especially since the data also comes in via form which means users couldn't submit a new (unique) value which we definitely want them to do since that's the ideal scenario.

[ScottWorld]

No, you can still do it the way you're doing it, but you will need to setup an extra automation to copy & paste the user-entered field into an additional linked record field, so you can get what you're looking for. My technique gives you exactly what you're looking for: the word "unique" or "duplicate" on each row.

[Mike_AutomaticN]

Hey @Mouser-IB-Admin!

The way I've handled this for different clients is by having an automation which gets triggered whenever e.g. Invoice # is created (or updated, depending on your use case).
As an action, the automation will run a script that will search for duplicates.
For last, the automation will use a specific field (e.g. "Alerts" field) to have it marked as "Duplicate" if the Invoice # already exists for a different record.

I'd be happy to show you around. Feel free to reach out.

Best,
Mike, Consultant @ Automatic Nation

[TheTimeSavingCo]

What if you had an automation that triggers whenever the form is submitted, and its actions would be:
1. "Find records" with the same Invoice # value
2. Conditional: If there's only one result (i.e. only the newly submitted record has that Invoice # value), then do nothing
3. Conditional: If there's more than one result (i.e. there are multiple records with the same Invoice # value), then mark the triggering record as a duplicate and email you

---
That works for new data, and if you need to identify duplicates with your existing data I'd suggest:
1. Duplicate the Invoice # field
2. Convert that new field into a linked field to a new table
3. Create a count field in the new table that outputs the number of invoices with that same Invoice #
4. Put a lookup of that count field in your main table

Not too sure what you'd like to do with the data after that, but with this you'll be able to see which Invoices have duplicate Invoice #s in your main table