Here’s the writeup. Note: I changed some field/table names from my original screen capture.
@M_k I created this solution using a copy of the base that you shared. Since it is your data, I didn’t want to share my copy of the base without your permission. Please let me know if I have your permission to share my copy of your base publicaly.
Purpose
A base has duplicate records, and the user needs a way of identifying and deleting records without using the Dedupe block or writing code.
Overview
This method relies on a combination of techniques:
- using a formula field to concatenate all the relevant fields together so that a single field can be used to determine uniqueness
- linking all the records in a table to the same record in another table for purposes of cross-record calculations.
Once each record has a copy if all the records in the table, a formula field can tell if the record is a duplicate or not.
New [MasterLookup] table
You need to create a new table with a single record. All of the records in the original table will need to be linked to this single record.
This table will have three fields:
- primary field (single line text with a value of “Link All Records”, or any other type/value of your choice)
- linked record field (allow linking to multiple records, and link to all records in the original table)
- lookup field (looking up the newly created {Composite Key + Record Id} field in the original table)
New fields in the original table
You need to create several fields in the original table.
Non-formula fields:
-
Linked record field to the single record in the new [MasterLookup] table. You will need to create this link for every record in the table.
-
Lookup of the {Composite Key + Record Id} field in the new [MasterLookup] table
Formula Fields:
-
{Composite Key} formula field, a concatenation of all the fields that must match for a record to be a duplicate (used to identify if a record is a duplicate) example: DATE & SUBJECT & NOTES
-
{Composite Key + Record Id} formula field, {Composite Key} & RECORD_ID(), a concatenation of all the fields must match, plus RECORD_ID() at the end (used to identify if a record is the “first” in a set of duplicates)
-
{Duplicate?} field that determines if the record is a duplicate that can be deleted
Creating the links
In order for this method to work, you must link all the records in the original table to the single record in the [MasterLookup] table. Here is the fastest way to link existing records:
- Create a formula field equal to the primary key of the single record in the [MasterLookup] table: “Link All Records”.
- Convert the formula field to a linked record field.
The {Duplicate?} formula field
This field ties everything together.
First the formula checks if the record is linked to the master table. If it isn’t, then the formula gives a warning.
Then the formula searches the combined data for all the records for both its {Composite Key}, and its {Composite Key + Record ID}, and returns the position of each string. It will find both of them, because the {Composite Key} is a subset of the {Composite Key + Record ID}.
If the two positions are the same, it is the “first” record, and should not be deleted. If the two positions are different, some other record must have the exact same composite key, but appear “earlier” in the array. Thus, this record is a duplicate and can be deleted.
Note that the {ALL_COMPOSITE_KEY+RECORD_ID} lookup field needs to be concatenated with an empty string to convert it from an array to a string.
IF(MasterLookup,
IF(
FIND({COMPOSITE_KEY+RECORD_ID},
{ALL_COMPOSITE_KEY+RECORD_ID} & ""
)
=
FIND(COMPOSITE_KEY,
{ALL_COMPOSITE_KEY+RECORD_ID} & ""
),
BLANK(),
"duplicate"
),
"Record needs to be linked"
)
Performance issues
This method is very resource intensive and can take a long time, especially as the number of records increases. The amount of data to be processed is the square of the number of records. If there are 1,000 records, an array of all 1,000 records needs to be stored in each of them, resulting in 1,000,000 records worth of info in the lookups alone! If there are many records, be prepared to wait a long time for Airtable’s calculations to catch up.
Deleting duplicates records
To delete duplicate records, create a view with filters to show only records that have “duplicate” in the {Duplicate?} field. Then delete all the records in that view.
You can automate the duplication with a 3rd party integration, such as Integromat or Zapier by having it delete new records in the view.
If you will be adding new records to the table that might be duplicates, those new records will also need to be linked to the [MasterLookup] table. You can also have an Integromat or Zapier to create those links for you. Create a new view with a filter to only include records where the {MasterLookup} linked field is blank, and create the link.
