SCRIPTING HELP: Flag Duplicate Records in a Table

[Sara_Ophoff]

I am trying to use a script in order to identify duplicate records in a table

Description:

1. Duplicate record field = Participant Name

Desired outcome:

1. When a duplicate record (repeated Participant Name) is created in the table labeled “Ops: Program Hub”
2. Add a CHECK in the field “Duplicate”

THIS IS THE SCRIPT I HAVE - and it is currently not working. I am VERY new to scripts and would appreciate any help!

---

// the table to check
let table = base.getTable(“Ops: Program Hub”);

// the record we’re searching for duplicates of
// we need to create a ‘recordId’ input variable connected to a record trigger
let config = input.config();
let recordId = config.recordId

// the field to save duplicates in. this should be a self-linked record field
let duplicatesField = table.getField(“Duplicate”)

// query the table and find our record according to its id:
let query = await table.selectRecordsAsync({fields: });
let record = query.getRecord(recordId);

// search for duplicates
let foundDuplicates = query.records.filter((potentialDuplicate) => {
// if they’re the exact same record, they’re not duplicates:
if (potentialDuplicate === record) {
return false;
}
if (potentialDuplicate.name === record.name) {
// if the names match, we’ve found a duplicate:
return true;
}
return false;
});

console.log(Found ${foundDuplicates.length} duplicates of ${record.name});
// save the duplicates:
await table.updateRecordAsync(record, {
[duplicatesField.name]: foundDuplicates,
});

[Nathaniel_Grano]

Hi @Sara_Ophoff,
It is definitely possible to use a script to check for duplicates. There’s quite a few issues with your current code.

Right now, your script looks like it’s trying to be a user-triggered script where the user specifies a single record to check and then it checks it. If so, you need a way for the user to say which record to check for duplicates. You’ll probably want to use something like this example for that: Record picker - Airtable Scripting

But, making the user say which record to check isn’t an ideal user experience. I’d suggest either create:

• An automation that runs whenever a record is updated and checks that record for duplicates using a script. If you do this, the automation can pass the script which record it should use
• A “check for duplicates” script that the user manually triggers that checks ALL records for duplicates. In this case you don’t need a specific recordId since you’ll check all of them

A few other issues with your current script:

• You should specify one or more fields in the table.selectRecordsAsync call. Right now you are passing in an empty array of fields. You need to tell Airtable which field(s) you want returned in the query.
• When actually comparing records to see if they are duplicates, you need to compare fields of each record, not the records themselves. Generally speaking you’ll use the record.getCellValueAsString() method to get each field value and then you can compare the two strings.

All that said, You don’t need a script to do this!
In another thread, I recently described a way to do duplicate checking that doesn’t require any scripts! See my latest reply here: Dupplications / look-up - #5 by Nathaniel_Granor

[Sara_Ophoff]

@Nathaniel_Granor thank you so much for this reply - and 100% agree, this script is far from being effective!

I will take a look at your automation suggestion that you linked and reach out if I have any questions! Excited to dig in there.

Thanks again!