I would love to have a feature that would search a given table by a field or set of fields to find and surface duplicate rows. It would then be great to be able to take a pre-defined action on duplicated rows such as delete, or merge. Dealing with dupes is always a pain with large data sets or importing new data into a set where some records may already exist. A way to deal with dupes in a somewhat automated way would be really helpful.
Here is another solution to this issue. It requires one relationship that you might already have.
Let’s say you have two tables: Contacts and Companies and you want to detect duplicate Emails in the Contacts table. The initial structure should look something like this:
Contacts
Email | Company (Link to)
Company
Company Name | Emails (link to)
In the Contacts table we’re going to add two columns, that we can later hide.
Col1: Rollup::Company::Emails with the function CONCATENATE(values)
Col2: Formula with the formula LEN({Col1}) != LEN(SUBSTITUTE({Col1},Email,"",2))
Hide these columns and create a conditional color change with the formula Where Col2 = 1 to create a highlight effect on duplicate emails.
Christoff,
The fact that the “Key” field can contain duplicates causes big problems for us when we convert columns of text to links - we can’t be sure which record gets the link.
-Donald
Donald, I’m not sure why you directed your comment to me. I am not an Airtable employee and I can’t solve the big problems that you’re experiencing with duplicate records.
Just adding my +1 for a dupe detection feature, I’m using Zapier to bring in form data and don’t want to duplicate contacts if they are already in the table.
Just of the top of my head…
(This would be directed to the users more experienced with programming/coding)
A solution to this problem would be to use AIrtable’s API…
It allows you to create,delete etc records in Airtable…
eg. Use case
When you want to import data in from Google sheets…
Use the Google SheetsAPI to store each record in a data structure(In case you are using JAVA to access the API you could use an ArrayList)
ONLY add the record in the data structure if a similar record does not exists.
After that you could loop over the data structure (which now consists of ONLY unique records) and use the Airtable API to insert it into a particular table.
David Smedberg, Great workaround. Thanks!
I agree with Roger (ripwit) … this should be a full-fledged feature that includes duplicate detection by column and an ability to designate at least one more column (other than the first “Name” column) as primary, unique and not-null in every table. (Note: A number of other similar DBs do have this feature.)
It sure has taken Airtable to implement this feature a long time!
+1 here for a duplicates detection and possibly pre-defined actions when they are detected. This is casually usefully in SO MANY scenarios. Specifically, I’m using Airtable for asset tracking using barcodes, and would like to know if someone has attempted to use the same asset ID number twice.
This feature looks like it’s something the user base want and was requested back in early 2016… can we get an official answer from Airtable on whether or not its been approved?
