Duplicate field

Hello I am trying to write a script that detects duplicates. Currently the if statement is triggered and it increase i(which tells me how many duplicates their are), even when the two fields do not match.

let table = base.getTable(“Policy”);
let view = table.getView(“All Fields”);
let first = table.getField(“PolicyNumber”); //Lookup Field
let sec = table.getField(“PolicyNumber”);

let result = await view.selectRecordsAsync({
sorts: [{field: “PolicyNumber”, direction: “asc”}]});

let result2 = await view.selectRecordsAsync({
sorts: [{field: “PolicyNumber”, direction: “asc”}]});

for (let record1 of result.records) {
let thedup = record1.getCellValue(first)
let i = 0;
output.text(thedup)
for (let record2 of result2.records){
let cop = record2.getCellValue(sec);
output.text(cop)
if (thedup=cop){
i++
if(i>1){
output.text(“help”)
}
}
}
}

This is the output i currently have 12 entires:
B0750RNMFP1709519 - 001
B0750RNMFP1709519 - 001
B0750RNMFP1709519 - 003
help
B0750RNMFP1709519 - 004
help
B0750RNMFP1709519 - 027
help
B0750RNMFP1709519 - 051
help
B0750RNMFP1709519 - 058
help
B0750RNMFP1709519 - 058
help
B0750RNMFP1709519 - 058
help
(the list goes on)
it basicly counts every option as = and increasing i with each interaction

Welcome to the community, @James_Liberto! :smiley: What account level is your workspace? If you have a Pro account or higher, you could use the Dedupe app to take care of the duplicates for you. If you want to go with a script, you’ll need to revise your structure a bit.

Instead of collecting the view records and field twice, only collect them once. (Actually, there’s no benefit by getting the field into a variable; we’ll just get its value by its name later on.)

let table = base.getTable("Policy");
let view = table.getView("All Fields");
let result = await view.selectRecordsAsync({
    sorts: [{field: "PolicyNumber", direction: "asc"}]
});

Make an empty array. This array will eventually contain all unique items from your collection. Also make a variable to contain the count of duplicates.

let items = [];
let duplicates = 0;

Loop through the collected records. If the policy number is not in the array, add it; if it is, increase the value of duplicates. (If you want to actually know which ones are duplicated, push each duplicate into a second array, then check its length at the end of the loop. I’ll skip that part, as you only mentioned knowing the total number of duplicates.)

for (let record of result.records) {
    let policyNumber = record.getCellValue("PolicyNumber");
    // If the item exists, increase the duplicates value
    if (items.includes(policyNumber))
        duplicates++;
    // Otherwise add the item to the items array
    else
        items.push(policyNumber);
}

Each time through the loop, any new item will be added to the items array, while an item that already exists in the array will increase duplicates by one.

Output as you wish. :slight_smile:

1 Like

Thank you so much this worked!!!

1 Like