Automation script tweak

[Jose_C]

Hi, all!
I like to use the script which is served in marketplace to delete duplicates.

But i want to use is as automation, so i need to to remove all input from user and validation in order to run it from a workflow.

I want to hardcode table, firstIdField, secondIdField, comparisonField

This is the code. Somebody can help me?

Thanks for your help!

Script

Edit code
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 = 100;
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’);
}

[Alexey_Gusev]

Jose_C:

But i want to use is as automation, so i need to to remove all input from user and validation in order to run it from a workflow.

Hi,
automation script usually has some input data and single record context.
Running dedupe-like action against single record doesn’t even require script and done by ‘find records’ action. Are you sure you choose the right tool?

but anyway, if you want to hardcode, you can use 2 ways (pay attention to the difference between field and fieldname, some functions accept both, some - object only, so you have to use 'table.getField(X)' instead of X
remove all code above
let { table, firstIdField, secondIdField, comparisonField } = settings;
and use any way you choose.

[Jose_C]

May thanks!

What i basically need, it is when a new record is created.

1. Look if a record with a specific content in a field ( Card ID in this case from Trello) exists
2. In that case instead to create a new record, update existing record , but only certain fields.

What is your point of view for this approach?

many thanks

[Alexey_Gusev]

Then you are certainly don’t need to use this script, except for learning purposes. But I’m afraid you can’t do it with ‘no-code’ at all.
I think you should be able to set a trigger, depending on how the record created, and set the action ‘Find records’.
But this action always returns array of records, even if just 1 found. Without code, you can’t retrieve record ID to edit. (Indeed, it’s possible by using additional ‘oneliner’ table with formula, but that’s too much.)

Also, AT haven’t ‘delete’ action, so you need to delete by script as well. When a new record created, and trigger runs, it already exists, and you have 2 records with the same Card ID, so you have to delete it

How to get single ID from array:

it tooks array and outputs single_id in variable ‘record_id’, which can be used in further steps

Note:
Setting ‘Create record’ as trigger can cause strange mistakes:

• when create manually, automation will run before Code ID inserted and Find Records will fail
• when created by program, you will probably will Find at least 1 record (just created), and 2 or more - if CODE match

you can use shift() instead of pop() to get element array from the start of array, because new created should be at the end (retrieved by pop()). but such position of IDs not guaranteed
so, if it’s your scenario, use id of new-created record as exclusion in such way:

i hope it’s still clear - .find searches in array and took first element that match x!=id
last two lines - how to delete a record.

you may ask ‘wait… why delete?, I need it’s values to put into old record in step 4’
the values are recorded at the automation start, and in History of runs you can check them.
so they will be still recorded in old record.

except if automaiton fail after delete but before update - the values are lost (but retrieveable from history). so you can split code and put deletion in step 5, after update.