Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Dec 25, 2023 06:26 PM
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.
Dec 26, 2023 08:45 AM
Hello,
I suppose your eco_id in second table is unique? Then you don't need script. Just add action 'update record' and put 'trim_eco_id' value into linked field. When pasting comma-separated list into linked field, Airtable gets it as array and links to each of values, doing the search in primary field.
It's a possible problem in other scenario, when you need to link record, containing comma in primary field. Then you'll need to put it into brackets, to inform " it's a single value, not a list of values "
Dec 29, 2023 01:15 PM - edited Dec 29, 2023 01:29 PM
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:
Dec 30, 2023 08:28 AM - edited Dec 30, 2023 04:43 PM
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 });
Dec 30, 2023 02:27 PM
Thank you! This is exactly what I needed. Appreciated.
Jan 02, 2024 10:06 PM
Generally, utilizing the user interface (UI) is preferable to scripting when possible. While many automation tasks can be accomplished through the UI, there are instances where scripting becomes necessary, such as performing actions like deleting records.
For future reference, your script contains some minor errors and a significant issue. The main problem lies in placing the update operation within the loop. Consequently, you're attempting to write values like 11229 to the field (represented by the object {'id:11229'}), followed by 11230 and so forth. Each new write operation overrides the previous one. Instead, it is advisable to create an empty array before the loop, push the objects into it within the loop (in your case, four objects), and then perform the write operation.
Jan 04, 2024 03:11 AM
"Great minds think alike" 😀
Jan 08, 2024 10:08 PM
It seems like.😁