Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Delete Duplicate but keep the latest record

Topic Labels: Automations
823 1
cancel
Showing results for 
Search instead for 
Did you mean: 
annslim
4 - Data Explorer
4 - Data Explorer

Hello, I would like to ask help on how I can modify this Delete Duplicate script from Airtable, so that it:

1) Runs automatically everytime

2) Deletes only the older record and keeps the most current one (based on Last Modified Time row)

Appreciate any 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: recordB, discard: recordA } : { keep: recordA, discard: recordB };
}

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');
}
1 Reply 1

Hi,
I don't think it's ever possible to run automatically everytime.
In your case, I would test each new record for duplicates in table, and then, if duplicate exists, delete it. The tricky part is to set correct trigger, Automation should run after field checked for duplicates filled with value, so it depends on a way you fill the data in table.