Help

Script to autolink an array field with an automation

2211 7
cancel
Showing results for 
Search instead for 
Did you mean: 
Drea_Burbank
5 - Automation Enthusiast
5 - Automation Enthusiast

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...

Screen Shot 2023-12-25 at 21.25.19.png

And too..

 Screen Shot 2023-12-25 at 21.25.29.png

 

Totally open to ideas here. 

7 Replies 7

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 "

Drea_Burbank
5 - Automation Enthusiast
5 - Automation Enthusiast

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: 

Screen Shot 2023-12-29 at 16.28.55.png

 

Screen Shot 2023-12-29 at 16.16.12.png

 

 

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,
Alexey_Gusev_0-1703615568845.png


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 });

 

 

 




 

Drea_Burbank
5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you! This is exactly what I needed. Appreciated. 

jhoneybairstow1
4 - Data Explorer
4 - Data Explorer

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.

"Great minds think alike" 😀

It seems like.😁