Duplicate field

Welcome to the community, @James_Liberto! :grinning_face_with_big_eyes: 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. :slightly_smiling_face:

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

let items = [];
let duplicates = 0;

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

Thank you so much this worked!!!