Skip to main content

Script to Link Same Table But Only Find Duplicates?

Jen_Rudd

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.

14 replies

Kamille_Parks11
Forum|alt.badge.img+15

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)

JonathanBowen
Kamille_Parks11 wrote:

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

JonathanBowen

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!

Pooja_Raman
Kamille_Parks11
2 people like this
  • Pooja_Raman
    Pooja_Raman
  • Kamille_Parks11
    Kamille_Parks11
Jen_Rudd
  • Jen_Rudd
  • Author
  • Inspiring
  • 49 replies
  • May 22, 2020
Kamille_Parks11 wrote:

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)


This is helpful, thanks!

Jen_Rudd
  • Jen_Rudd
  • Author
  • Inspiring
  • 49 replies
  • May 22, 2020
JonathanBowen wrote:

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!


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.

V
1 person likes this
  • V
    VictoriaPlummer
Pooja_Raman
Forum|alt.badge.img
JonathanBowen wrote:

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!


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

JonathanBowen
1 person likes this
  • JonathanBowen
    JonathanBowen
Jen_Rudd
  • Jen_Rudd
  • Author
  • Inspiring
  • 49 replies
  • September 28, 2020
JonathanBowen wrote:

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!


@JonathanBowen how would I adjust the code if the record name is not equal to the email, the email is one of the fields in the record?

Thanks for your help!

JonathanBowen
  • JonathanBowen
  • Inspiring
  • 1110 replies
  • September 28, 2020
Jen_Rudd wrote:

@JonathanBowen how would I adjust the code if the record name is not equal to the email, the email is one of the fields in the record?

Thanks for your help!


Hi @Grow_With_Jen - this is the bit you want to change:

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

This should become:

// make an array of all of the emails
let emails = applicantsQuery.records.map(record => {
    return record.getCellValue('NAME OF YOUR EMAIL FIELD HERE')
})
Jen_Rudd
  • Jen_Rudd
  • Author
  • Inspiring
  • 49 replies
  • September 28, 2020
JonathanBowen wrote:

Hi @Grow_With_Jen - this is the bit you want to change:

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

This should become:

// make an array of all of the emails
let emails = applicantsQuery.records.map(record => {
    return record.getCellValue('NAME OF YOUR EMAIL FIELD HERE')
})

@JonathanBowen, AMAZING! Thank you so much.

M
  • Maira_Masson
  • New Participant
  • 3 replies
  • October 22, 2021
JonathanBowen wrote:

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!


Hi Jonathan! How are you?
I’m trying to use this code to mark duplicates in my table, but I don’t get any phrase in the “duplicate” field.
I just change the name of the table, is called “juli”.
The script runs succesfully, but even then, I don’t get “this is a duplicate” or anything really.
Is it OK that I’m writing this code inside an automation?
I’ve already try with two triggers:

  • If I enter a new record
  • Every 15 minutes
    But either way, I don’t get any result.
    Thank you in advance!

Here is the code:

let applicantsTbl = base.getTable(‘juli’);

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'

        })

    }

}

}

M
  • Maira_Masson
  • New Participant
  • 3 replies
  • November 9, 2021
Maira_Masson wrote:

Hi Jonathan! How are you?
I’m trying to use this code to mark duplicates in my table, but I don’t get any phrase in the “duplicate” field.
I just change the name of the table, is called “juli”.
The script runs succesfully, but even then, I don’t get “this is a duplicate” or anything really.
Is it OK that I’m writing this code inside an automation?
I’ve already try with two triggers:

  • If I enter a new record
  • Every 15 minutes
    But either way, I don’t get any result.
    Thank you in advance!

Here is the code:

let applicantsTbl = base.getTable(‘juli’);

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'

        })

    }

}

}


@Noamsay can you maybe help me with this script? Thanks!

JonathanBowen
Maira_Masson wrote:

Hi Jonathan! How are you?
I’m trying to use this code to mark duplicates in my table, but I don’t get any phrase in the “duplicate” field.
I just change the name of the table, is called “juli”.
The script runs succesfully, but even then, I don’t get “this is a duplicate” or anything really.
Is it OK that I’m writing this code inside an automation?
I’ve already try with two triggers:

  • If I enter a new record
  • Every 15 minutes
    But either way, I don’t get any result.
    Thank you in advance!

Here is the code:

let applicantsTbl = base.getTable(‘juli’);

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'

        })

    }

}

}


Hi @Maira_Masson - I think this is the same problem noted further up the chain. The primary field in your table is Name and Email is one of the other fields. If we want to match on Email (which is what the script does towards the end), you need to change this part of the script:

let emails = applicantsQuery.records.map(record => {
  return record.name
})

to this:

let emails = applicantsQuery.records.map(record => {
  return record.getCellValue('Email')
})
M
  • Maira_Masson
  • New Participant
  • 3 replies
  • November 15, 2021
JonathanBowen wrote:

Hi @Maira_Masson - I think this is the same problem noted further up the chain. The primary field in your table is Name and Email is one of the other fields. If we want to match on Email (which is what the script does towards the end), you need to change this part of the script:

let emails = applicantsQuery.records.map(record => {
  return record.name
})

to this:

let emails = applicantsQuery.records.map(record => {
  return record.getCellValue('Email')
})

Thank you!! It’s working now.
I need to adapt this script to be use in an URL field. We need to mark the duplicates comparing between LinkedIn profiles URLs, instead of Emails. How should I adapt this?

Thank you in advance!

JonathanBowen
Maira_Masson wrote:

Thank you!! It’s working now.
I need to adapt this script to be use in an URL field. We need to mark the duplicates comparing between LinkedIn profiles URLs, instead of Emails. How should I adapt this?

Thank you in advance!


@Maira_Masson - you need to reference the url field instead of the email field, so two changes I think:

This:

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

becomes:

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

and this:

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'
            })
        }
    }
}

becomes:

for (let record of applicantsQuery.records) {
    for (let dupe of uniqueDupes) {
        if (record.getCellValue('URL') == dupe) {
            await applicantsTbl.updateRecordAsync(record, {
                'Duplicate': 'This has a duplicate'
            })
        }
    }
}

I haven’t tested this out, but it should work OK.

Reply


Most helpful members this week

Terms & Conditions