Script to Link Same Table But Only Find Duplicates?

Ok, I have this script, but I need to check only for duplicates in the same table. Thoughts on what I need to add to ensure the record pulled is not self linking in linked records?

//Define the table and query

let jobTbl = base.getTable(“Caregiver Applicants”);

let appQuery = await jobTbl.selectRecordsAsync();

//Loop through the records and find the Applicant

for (let record of appQuery.records) {

 let appname = record.getCellValue("E-mail");



//Define linked table and query

let applicantTbl = base.getTable("Caregiver Applicants");

let applicantQuery = await applicantTbl.selectRecordsAsync();



//Loop through linked table and match ID values

for (let appRecord of applicantQuery.records) {

    if (appRecord.getCellValue("E-mail") === appname) {

        let inputid = appRecord.id;

     

        //Update field

        jobTbl.updateRecordAsync(record, {

            "Additional Records": [{id: inputid}]

        });

   }

}

}

This ends up linking the original record to itself. Ugh.

You could add a filter or another condition to your if() statement to check the record ID.

First get the record ID of the original record: appid = record.id

Then redefine your if statement to: if(appRecord.getCellValue("E-mail") === appname && appRecord.id !== appid)

Hi @Kamille_Parks, @Grow_With_Jen - I tried the same as @Kamille_Parks, but there might be a problem with this. If there are 3 instances of an email, then you want each of them to link to the other two. However, the simple update clears the cell of any existing links. According to the docs you need to use the spread operator like this:

myTable.updateRecordAsync(myRecord, {
    'myLinkedRecordField': [
        ...myRecord.getCellValue('myLinkedRecordField'),
        { id: newForeignRecordIdToLink }
    ]
})

to add the new array element to the existing array elements. However, I cannot get this to work :slightly_frowning_face:

The error is something like:

myRecord.getCellValue('myLinkedRecordField') is not iterable

This is an alternative approach, but might not be what you are looking for. It identifies duplicates, but doesn’t show which records are connected as duplicates.

let applicantsTbl = base.getTable('Applicants');
let applicantsQuery = await applicantsTbl.selectRecordsAsync();


// make an array of all of the emails
let emails = applicantsQuery.records.map(record => {
    return record.name
})

// filter to get emails which are duplicates of another
let dupes = emails.filter((item, index) => emails.indexOf(item) != index);
// refine this with Set to the unique set of duplicates
let uniqueDupes = [...new Set(dupes)];

// iterate through the records and update this with a duplicate
for (let record of applicantsQuery.records) {
    for (let dupe of uniqueDupes) {
        if (record.getCellValue('Email') == dupe) {
            await applicantsTbl.updateRecordAsync(record, {
                'Duplicate': 'This has a duplicate'
            })
        }
    }
}

You end up with something like this:

The above approach (earlier posts) is better - will post again if I can figure it out!

2 Likes

This is helpful, thanks!

I like this idea too. They don’t necessarily need all records linked theoretically, that does leave room for error, but the existence of a duplicate is a good flag.

1 Like

Just used this script and it worked perfectly - thanks for sharing Jonathan!

1 Like

Thanks for all your help before. Finally able to look at this and I feel like I am missing something.

I got stuck on line 37:

       //Define the table and query

let jobTbl = base.getTable('Caregiver Applicants');

let appQuery = await jobTbl.selectRecordsAsync();

//Loop through the records and find the Applicant

for (let record of appQuery.records) {

 let appname = record.getCellValue("Lowercase Email for Searching");



//Define linked table and query

let applicantTbl = base.getTable("Caregiver Applicants");

let applicantQuery = await applicantTbl.selectRecordsAsync();



//Loop through linked table and match ID values

for (let appRecord of applicantQuery.records) {

    if (appRecord.getCellValue("Lowercase Email for Searching") === appname) {

        let inputid = appRecord.id;

     

        //Update field

        jobTbl.updateRecordAsync(appRecord.id, {
    'Duplicate Of': [
        ...applicantQuery.appRecord.id('Duplicate Of'),
        { id: appRecord.id }
    ]
})

   }

}
}