Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jul 22, 2022 08:22 AM
Hi,
I have written a very simple duplicate validation script that is triggered from a record create event, but it doesn’t work from the automation and it works fine in the extension.
This is the action script code:
// Input variables setup
let inputConfig = input.config();
let clientID = inputConfig.clientID;
let clientName = inputConfig.clientName;
let clientRename = clientName;
// Query Resultset
let table = base.getTable('Clientes');
let view = table.getView('Clientes Duplicados');
let queryResult = await view.selectRecordsAsync({fields: []});
// Duplicate Validation
let regex = /\[+\d+\]/g;
let duplicatesCounter = 0;
for (let record of queryResult.records) {
if (record.name.toLowerCase().replace(regex,'').trim() == clientName.toLowerCase().trim()) {
duplicatesCounter++;
};
}
if (duplicatesCounter > 1) {
clientRename = clientName + " [" + duplicatesCounter + "]";
await table.updateRecordAsync (clientID, {
"Nombre" : clientRename
});
};
output.set('clientRename', clientRename);
This is the extension code:
//Values to match
let clientID = 'rec3CvbN38zLEEGgR';
let clientName = 'Canais';
let clientRename = clientName;
// Query Resultset
let table = base.getTable('Clientes');
let view = table.getView('Clientes Duplicados');
let queryResult = await view.selectRecordsAsync({fields: []});
// Duplicates validation
let regex = /\[+\d+\]/g;
let duplicatesCounter = 0;
for (let record of queryResult.records) {
if (record.name.toLowerCase().replace(regex,'').trim() == clientName.toLowerCase().trim()) {
duplicatesCounter++;
};
console.log (`
${record.name.toLowerCase().replace(regex,'').trim()}
${clientName.toLowerCase().trim()}
${duplicatesCounter}`);
}
if (duplicatesCounter > 1) {
clientRename = clientName + " [" + duplicatesCounter + "]";
await table.updateRecordAsync (clientID, {
"Nombre" : clientRename
});
};
These are the results:
Before run extension script
Nombre | RecordID | Tipo |
---|---|---|
Alfa | recFcZFOXZ3HLceqf | Cliente |
Beta | recwRqpeWKLhlisHH | Cliente |
Canais | rec1sz2GEKuJbdXQh | Mandante |
Canais | rec3CvbN38zLEEGgR | Final |
Canais [1] | recM6JRtAtTsP91bz | Matriz |
Canais [2] | recOzEQzIp92lVUwT | Cliente |
Gamma | recZPc4mAYD81M7Dj | Matriz |
Delta | recUeLOADTASjL4t5 | Cliente |
Epsilon | recHdvqDrwPITsGx0 | Cliente |
Zeta | recetQgGdTdJlaP5i | Cliente |
Eta | recOByLdOtacr10bk | Cliente |
Theta | recf6eFw2JIDfBVJx | Mandante |
After run extension script
Nombre | RecordID | Tipo |
---|---|---|
Alfa | recFcZFOXZ3HLceqf | Cliente |
Beta | recwRqpeWKLhlisHH | Cliente |
Canais | rec1sz2GEKuJbdXQh | Mandante |
Canais [1] | recM6JRtAtTsP91bz | Matriz |
Canais [2] | recOzEQzIp92lVUwT | Cliente |
Canais [4] | rec3CvbN38zLEEGgR | Final |
Gamma | recZPc4mAYD81M7Dj | Matriz |
Delta | recUeLOADTASjL4t5 | Cliente |
Epsilon | recHdvqDrwPITsGx0 | Cliente |
Zeta | recetQgGdTdJlaP5i | Cliente |
Eta | recOByLdOtacr10bk | Cliente |
Theta | recf6eFw2JIDfBVJx | Mandante |
Console Log
CONSOLE LOG
“alfa
canais
0”
“beta
canais
0”
“canais
canais
1”
“canais
canais
2”
“canais
canais
3”
“canais
canais
4”
“gamma
canais
0”
“delta
canais
0”
“epsilon
canais
0”
“epsilon
canais
0”
“zeta
canais
0”
“eta
canais
0”
“theta
canais
0”
Before run Create Trigger
Nombre | RecordID | Tipo |
---|---|---|
Alfa | recFcZFOXZ3HLceqf | Cliente |
Beta | recwRqpeWKLhlisHH | Cliente |
Canais | rec1sz2GEKuJbdXQh | Mandante |
Canais [1] | recM6JRtAtTsP91bz | Matriz |
Canais [2] | recOzEQzIp92lVUwT | Cliente |
Canais [4] | rec3CvbN38zLEEGgR | Final |
Gamma | recZPc4mAYD81M7Dj | Matriz |
Delta | recUeLOADTASjL4t5 | Cliente |
Epsilon | recHdvqDrwPITsGx0 | Cliente |
Zeta | recetQgGdTdJlaP5i | Cliente |
Eta | recOByLdOtacr10bk | Cliente |
Theta | recf6eFw2JIDfBVJx | Mandante |
After run Create Trigger
Nombre | RecordID | Tipo |
---|---|---|
Alfa | recFcZFOXZ3HLceqf | Cliente |
Beta | recwRqpeWKLhlisHH | Cliente |
Canais | rec1sz2GEKuJbdXQh | Mandante |
Canais | recJUkM5aolLFadhH | Mandante |
Canais [1] | recM6JRtAtTsP91bz | Matriz |
Canais [2] | recOzEQzIp92lVUwT | Cliente |
Canais [4] | rec3CvbN38zLEEGgR | Final |
Gamma | recZPc4mAYD81M7Dj | Matriz |
Delta | recUeLOADTASjL4t5 | Cliente |
Epsilon | recHdvqDrwPITsGx0 | Cliente |
Zeta | recetQgGdTdJlaP5i | Cliente |
Eta | recOByLdOtacr10bk | Cliente |
Theta | recf6eFw2JIDfBVJx | Mandante |
Could someone tell me where the error is? Even though I’ve reviewed it, I can’t find any differences in the code…
Thanks in advance
Jul 22, 2022 06:04 PM
Hi,
What’s the output of output.set(‘clientRename’, clientRename); ?
I think you should also check duplicatesCounter
upd: yes, counter was 0 and the reason : record.name ='Unnamed record'
inside loop.
with record.getCellValueAsString(‘your_primary_field’) it works OK.
interesting case.
Jul 22, 2022 07:05 PM
btw, it works ok with record.name, when for loop not used
let {clientID,clientName} = input.config();
let table = base.getTable('Clientes');
let view = table.getView('Clientes Duplicados');
let query = await view.selectRecordsAsync({fields: []});
let val=x=>x.toLowerCase().replace(/\[+\d+\]/g,'').trim()
let dupes=query.records.filter(r=>val(r.name)==val(clientName))
let update={'Nombre':clientName+'['+dupes.length+']'}
if (dupes.length>1) await table.updateRecordAsync(clientID,update)
Jul 23, 2022 02:58 AM
Hi Alexey,
First of all, thanks for your excellent contribution and quick response, unfortunately it still doesn’t work when fired from the trigger (action script), but it works fine when run from the extension script (same as my code).
The record is executed from an Airtable interface and, in theory, the record should be created only when the Submit button is pressed, or so I have interpreted from the documentation.
Once the submit button is pressed, the automation should be triggered, which has this structure:
TRIGGER
ACTIONS
I suspect that the ACTUAL write operation to the table occurs when the automation completes and therefore it is the update in the script that fails, but I am new to Airtable and I am probably wrong and there is another reason why that the script does not work, in any case, I really appreciate the interest and the time you have dedicated to trying to help me, thank you very much.
Best regards,
PS: I really liked the elegance of your code and the economy of resources used :winking_face:
Jul 23, 2022 11:03 AM
Yes, such automation behavior happens for some users, you can search here to read more. I never encountered such issue, but remember that solution included some additional field(s) with ‘Date created’ and view with some delay (5 minutes?) and trigger changed to “When record enters view”.
You can dig into run history to be sure. Like this
Maybe you can install script step before your actual script, with some delay and avoid extra fields. No timeout/sleep/delay command, but you can, for example
try {const responce = await fetch('https://somesite.com')
console.log(responce.status===200? 'ok':'not ok')}
catch {console.log('not ok')}
But then, in your actual step, you cannot rely on input.config().clientName, because it’s value defined at the automation start. Instead, you should query it using ID (which does not change after creation)
query.records.getrecord(id).name //or query.records.getrecord(id).getcellvalue('field')
Jul 24, 2022 06:09 AM
Hi Alexey,
I’ve finally gotten it to work, although I’m not quite sure how since I’ve changed so many things that now I have no idea which one has produced the miracle.
I changed the approach in relation to the trigger that fired the event and it didn’t work either, I changed the code and introduced the following modifications (that I remember):
let query = await view.selectRecordsAsync({fields: ['Name']});
let clientClean = val(clientName)
r=>val(r.getCellValue('Name')
I’m sure I’ve made a lot more changes and gone back to the origin several times so I may have changed something else, the end result is that it now works although I had to do some tweaking to get the duplicate numbering to work as expected. (the same way Windows File Explorer works with duplicate files)
Again, thank you for the enormous and selfless help you have given me.
Best regards,