Jan 31, 2022 08:07 PM
I spent a long time trying to figure a solution to this need, so now that I found something that works, I want to share it with other users.
I don’t know scripting, but I can copy/paste other’s code. So here’s a non-technical solution to automatically deleting duplicates/deleting records using automations and scripts.
In this case, I wanted to delete the oldest record, since the newest record is going to be the most up-to-date.
Step 1. Mark duplicates: Create an automation that triggers when a new record is created. Then use the mark duplicate script provided by AirTable.
Step 2. Separate the older duplicate records: Create a view that filters by duplicate status and date of record creation. In this case, I filter by Duplicate? = “yes” (or however you set up the previous step) and record creation date “is not” “today”. That way only the older duplicate enters into this view.
Step 3. Delete the older duplicates: Create another automation that triggers whenever a record enters this view. The resulting action of this automation should be a script that automatically deletes records. Replace “table name” and “view name” in the code below:
let table = base.getTable("Table Name"); let view = table.getView("View Name"); let query = await view.selectRecordsAsync(); let recordId = query.records.id; await table.deleteRecordAsync(recordId); console.log("Deleted a record!");
Of course, you can create a script that does it all at once. If you do, please post and explain for us lay people! But none of the sample code provided in these forums made much sense to me as a person without scripting experience, so I create this solution instead.
Jan 31, 2022 08:26 PM
Welcome to the Airtable community, and thank you for sharing your solution!
May 06, 2022 04:53 AM
Thanks for the explanation it helps a lot for a guy who doesn’t know script like you ! What kind of filter can you use in order to keep the most recent record ? In your example, if you duplicate a record the same day as it was created, your filter wouldn’t work right ?
May 07, 2022 01:34 PM
Another 2 ways
First, quick, if the number of dupes is low
sorted by frequency:
Second, to mark dupes and their number
copy-paste whole column (i use ctrl+C/ctrl+v)
after you turn lookup ‘count’ to single text or number ‘static’ field, you can remove extra field and table