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.
Are any of you using duplicate preventing processes or procedures you’d recommend beyond - “Hey, check the DB before you add a new record?” I am setting up a system and have yet to release it to the team. When I do, I would like to specify procedural guidelines for quality data curation. Thank you.
Duplicate checking is important when collaborating. As far as I can tell, if I share a form, invitees can’t see if a name has already been added to a base, and they are able to re-add a name without warning. They are submitting names of people that could be donation prospects, and some of our best prospects will be recommended multiple times.
It appears I would really need to share the base in grid view rather than a form.
Hi all! I found a workaround to help find and delete duplicate records. As is, this only works for deleting perfect duplicates, it does NOT merge records together into a single record.
Imagine a table with 3 records: John Smith, John Smith, and Abraham Lincoln.
First, create an auto number field.
Next, ensure you have a single line text field that contains the same text for each duplicate record (“John Smith”) but is unique for records that are not duplicates (“Abraham Lincoln”).
Next, turn this field into a link to a new table (I called it uniques). Now, you have a record for “Abraham Lincoln” that has 1 linked record (no duplicates found), and you have a record for “John Smith” with 2 linked records (duplicates found).
In the “uniques” table, create a rollup of the autonumber field, using the “MIN” formula. So, for John Smith, this field will now contain 1, and for Abraham Lincoln it will contain 3.
Back in the main table, create a lookup field, using the link to “uniques” and the rollup field you created within uniques.
Now (again the main table), create a formula, which will look something like this:
IF(LOOKUP_FIELD<AUTONUMBER_FIELD,“Delete”,“Do Not Delete”)
Now, the first John Smith and the Abraham Lincoln record will show “Do not Delete”, but the second John Smith will show “Delete”.
I’m sure that you could use an “array” type rollup to attempt to merge fields in the duplicate records (or least to see if there are multiple or conflicting entries in certain fields).
Hi all! I found a workaround to help find and delete duplicate records. As is, this only works for deleting perfect duplicates, it does NOT merge records together into a single record.
Imagine a table with 3 records: John Smith, John Smith, and Abraham Lincoln.
First, create an auto number field.
Next, ensure you have a field that contains the same text for each duplicate record (“John Smith”) but is unique for records that do are not duplicates (“Abraham Lincoln”).
Next, turn this field into a link to a new table (I called it uniques). Now, you have a record for “Abraham Lincoln” that has 1 linked record (no duplicates found), and you have a record for “John Smith” with 2 linked records (duplicates found).
In the “uniques” table, create a rollup of the autonumber field, using the “MIN” formula. So, for John Smith, this field will now contain 1, and for Abraham Lincoln it will show 3.
Back in the main table, create a lookup field on the link to “uniques” and lookup for the rollup field you created.
Now, create a formula, which will look something like this:
IF(LOOKUP_FIELD<AUTONUMBER_FIELD,“Delete”,“Do Not Delete”)
Now, the first John Smith record will show “Do not Delete”, but the second John Smith will show “Delete”.
OMG, please. In an agile workplace leveraging ad-hoc tools, Airtable evangelists are often asked to ingest and “Airtable” (upgrade) spreadsheets of dubious quality. The time it takes to cleanse such data far exceeds the the effort to make the imported work truly relational (and useful). For now, we rely on Excel/Numbers to rework files before uploading them to Airtable. This isn’t fast.
Wait, we can’t find and delete duplicate rows… Well, Sticking with google sheets for now then. I have thousands of data i would like to import and some maybe duplicates. I can’t imagine a excel like tool that doesn’t have this feature.
Chiming in here as a request to add duplicate detection rules, ability to expose UIDs to make importing related records easier, and also a simple merge feature would be a tremendous help.
Thanks for the awesome work. I just discovered Airtable and couldn’t be more excited about it!
