Help

Duplicate script from 2 to 3 fields and from 1 comparison to 2 comparison fields

Topic Labels: Extensions Formulas
433 0
cancel
Showing results for 
Search instead for 
Did you mean: 
cmeck11
4 - Data Explorer
4 - Data Explorer

Hi Everyone!

I'm looking for some help editing this script from the marketplace.

Right now, I can select 2 fields for the duplicate comparison but I actually need 3. How can I change this to have it compare 3 fields instead of 2?

Also, this script has the comparison field where it will choose the record with based on this field. I actually need to have 2 different options for this field. Can this be done and how do I do that?

Thanks for the help!

 

let settings = input.config({
    title: 'Delete duplicates',
    description: `This script will delete duplicate records in a given table according to the value of
two input fields. Duplicate records are detected when they contain the same cell value for each identifying
field. For any two records that are considered duplicates, it will use a third comparison field to determine
which of the two records should be deleted.`,
    items: [
        input.config.table('table', { label: 'Table' }),
        input.config.field('firstIdField', {
            parentTable: 'table',
            label: 'First identifying field',
        }),
        input.config.field('secondIdField', {
            parentTable: 'table',
            label: 'Second identifying field',
        }),
        input.config.field('comparisonField', { parentTable: 'table', label: 'Comparison field' }),
    ],
});

let { table, firstIdField, secondIdField, comparisonField } = settings;

// Airtable limits batch operations to 50 records or fewer.
let maxRecordsPerCall = 50;

function choose(recordA, recordB) {
    let valueA = recordA.getCellValueAsString(comparisonField);
    let valueB = recordB.getCellValueAsString(comparisonField);
    return valueA > valueB ? { keep: recordA, discard: recordB } : { keep: recordB, discard: recordA };
}

let existing = Object.create(null);
let toDelete = [];

// Part 1: Identify duplicate records in need of deletion
//
// We don't modify the table contents in this Part in the interest of
// efficiency. This script may trigger a large number of deletions, and it's
// much faster to request that they be done in batches. When we identify a
// record that should be deleted, we add it to an array so we can batch the
// operations in Part 3 of the script.
let query = await table.selectRecordsAsync({
    fields: [firstIdField, secondIdField, comparisonField],
});

for (let record of query.records) {
    let key = JSON.stringify([
        record.getCellValue(firstIdField),
        record.getCellValue(secondIdField),
    ]);

    // If we've already encountered a record with identical field values,
    // either that record or the current record need to be removed.
    if (key in existing) {
        let { keep, discard } = choose(record, existing[key]);
        toDelete.push(discard);
        existing[key] = keep;

        // If this is the first time we've observed a record with these
        // particular field values, make a note of it so we can recognize
        // duplicates as we iterate through the rest.
    } else {
        existing[key] = record;
    }
}

// Part 2: Verify
//
// Inform the script's user of the changes to be made and await their
// confirmation.
output.markdown(`Identified **${toDelete.length}** records in need of deletion.`);

let decision = await input.buttonsAsync('Proceed?', ['Yes', 'No']);

// Part 3: Execute the necessary operations

if (decision === 'No') {
    output.text('Operation cancelled.');
} else {
    output.text('Applying changes...');

    while (toDelete.length > 0) {
        await table.deleteRecordsAsync(toDelete.slice(0, maxRecordsPerCall));
        toDelete = toDelete.slice(maxRecordsPerCall);
    }

    output.text('Done');
}
0 Replies 0