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.