Help

Re: Show if a record is duplicated

Solved
Jump to Solution
2084 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Edo_Vigo
5 - Automation Enthusiast
5 - Automation Enthusiast

Is there any way to show if a record has a certain field already present in another record in the view/table?

For example show that the Airtable link is present 2 times
Schermata 2021-07-01 alle 17.13.40

Thx

1 Solution

Accepted Solutions

This script, and most/all scripts from the Marketplace, are designed to work in the Scripting App which is not the same thing as a Run a script action within an Automation.

You will have to make several edits to the script to get it to work:

  • Delete everything above the line: // Airtable limits batch...
  • Declare your variables by adding the following code to the top:
let table = base.getTable("Insert Name")
let firstIdField = table.getField("Insert Name")
let secondIdField = table.getField("Insert Name")
let comparisonField  = table.getField("Insert Name")
  • Delete every line that starts with output.
  • Replace everything from Part 2 to Part 3 with the simiplified version of its code, taking away human intervention:
while (toDelete.length > 0) {
    await table.deleteRecordsAsync(toDelete.slice(0, maxRecordsPerCall));
    toDelete = toDelete.slice(maxRecordsPerCall);
}

when posting long blocks of code in the forums, be sure to put ``` on the lines above and below the code so its formatted nicely.

See Solution in Thread

3 Replies 3

You could use the Dedupe App:

or the Delete Duplicates script:

Oh wuao thx. I’ve tried to add the script available in the marketplace to an automation.
But when I run the test I have this error ==> TypeError: input.config.table is not a function at main on line 8

Do you know what i’m doing wrong

The script is:
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');

}

This script, and most/all scripts from the Marketplace, are designed to work in the Scripting App which is not the same thing as a Run a script action within an Automation.

You will have to make several edits to the script to get it to work:

  • Delete everything above the line: // Airtable limits batch...
  • Declare your variables by adding the following code to the top:
let table = base.getTable("Insert Name")
let firstIdField = table.getField("Insert Name")
let secondIdField = table.getField("Insert Name")
let comparisonField  = table.getField("Insert Name")
  • Delete every line that starts with output.
  • Replace everything from Part 2 to Part 3 with the simiplified version of its code, taking away human intervention:
while (toDelete.length > 0) {
    await table.deleteRecordsAsync(toDelete.slice(0, maxRecordsPerCall));
    toDelete = toDelete.slice(maxRecordsPerCall);
}

when posting long blocks of code in the forums, be sure to put ``` on the lines above and below the code so its formatted nicely.