Re: Script to autolink an array field with an automation
I'm so stumped on this. I've been creating automation to autolink my records, but now I have a messy array field and I cannot for the life of me figure out how to autolink it. I've tried six ways from Sunday using all the code available here.
Here's the code I've been using:
//Define the main table and query
let mainTbl = base.getTable("BIOD CALC");
let mainQuery = await mainTbl.selectRecordsAsync();
//Define link table and query
let linkTbl = base.getTable("ECO ↔ CREDIT");
let linkQuery = await linkTbl.selectRecordsAsync();
//Loop through the records and find the field to update
for (let mainRecord of mainQuery.records) {
let mainId = mainRecord.getCellValue("ECO ↔ CREDIT");
//Loop through linked table and match ID values
for (let linkRecord of linkQuery.records) {
if (linkRecord.getCellValue("eco_id") === mainId) {
//Update field
mainTbl.updateRecordAsync(mainRecord, {
'ECO ↔ CREDIT': [{id: linkRecord.id}]
});
}
}
}Here's some code I tried that didn't work.
let inputConfig = input.config();
console.log(`The value of trim_eco_id is ${inputConfig.trim_eco_id}`);
//Define the main table and query
let mainTbl = base.getTable("BIOD CALC");
let mainQuery = await mainTbl.selectRecordsAsync();
//Define link table and query
let linkTbl = base.getTable("ECO ↔ CREDIT");
let linkQuery = await linkTbl.selectRecordsAsync();
inputConfig['trim_eco_id'].toString().split(',').forEach((s) => {
console.log(s.trim());
//Loop through linked table and match ID values
for (let linkRecord of linkQuery.records) {
if (linkRecord.getCellValue("eco_id") === s.trim()) {
//Update field
mainTbl.updateRecordAsync(mainRecord, {
'ECO ↔ CREDIT': [{id:`${s.trim()}`}]
});
}
}
});
Here's the base I'm trying to link from...
And too..
Totally open to ideas here.
Okay I still think javascript would be much simpler than Airtables convoluted interface. But I finally figured it out.
For other people, here is the final configuration:
Usually if it can be done with UI only, it'd better than scripting.
Sometimes you can do most part of automation with UI and script actions that cannot be done without it, like delete the record.
Just in case you will need this in future,
Your script has some minor mistakes and one major problem - you are placed update operation into the loop, so you try to write 11229 to field (i mean object {'id:11229'} ), then 11230 and so on, and each new write will erase previous. Instead, you should create empty array before loop, then push object to it inside the loop (4 objects in your case), and then do write operation outside the loop.
Also, all async procedure calls must be done with await,
await mainTbl.updateRecordAsync(mainRecord, {
'ECO ↔ CREDIT': [{id:`${s.trim()}`}]
});
and you have mainrecord variable, which is from loop of initial script, but in your updated version it is appear from nowhere.
But actually, if you choose to write scripts in future automations, you can do this easier with ES6 features: arrow functions, 'array-helpers' like map, filter etc
For your case, I would pass cell value and record id,
and do something like (I didn't test it, just for example)
// Another ES6 feature - deconstruct input.config() in 1 step, no additional variables
let {trim_eco_id, mainrecord_id} = input.config();
console.log(`The value of trim_eco_id is ${inputConfig.trim_eco_id}`);
//Define the main table. No need to query it
let mainTbl = base.getTable("BIOD CALC");
//Define link table and query
let linkTbl = base.getTable("ECO ↔ CREDIT");
let linkQuery = await linkTbl.selectRecordsAsync();
//get array of values, get only records with eco_id present in this array
let toLink=trim_eco_id.toString().split(',').map(val=>val.trim())
let records_to_link=linkQuery.filter(rec=>toLink.includes(rec.getCellValue("eco_id")))
//create object to write from filtered records list and write it
let arrayToWrite=records_to_link.map(rec=>( {id:rec.id} ))
await mainTbl.updateRecordAsync(mainrecord_id, {'ECO ↔ CREDIT': arrayToWrite });
