Skip to main content

Script to autolink an array field with an automation

  • December 26, 2023
  • 7 replies
  • 69 views
D
Forum|alt.badge.img+4

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. 

    7 replies

    Alexey_Gusev
    Forum|alt.badge.img+25

    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 "

    D
    Forum|alt.badge.img+4
    • Drea_Burbank
    • Author
    • New Participant
    • December 29, 2023

    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: 

     

     

     

     

    Alexey_Gusev
    Forum|alt.badge.img+25

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

     

     

     




     

    D
    Forum|alt.badge.img+4
    • Drea_Burbank
    • Author
    • New Participant
    • December 30, 2023

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

    J
    Forum|alt.badge.img+2
    • jhoneybairstow1
    • Participating Frequently
    • January 3, 2024

    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.

    Alexey_Gusev
    Forum|alt.badge.img+25

    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"  😀

    J
    Forum|alt.badge.img+2
    • jhoneybairstow1
    • Participating Frequently
    • January 9, 2024

    "Great minds think alike"  😀


    It seems like.😁

     

    Terms & ConditionsAccessibility statement