Script to update value from another table based on matching records

[April_Urban]

Hello! I'm trying to write a script that I can run to populate a field in one table with a value that lives in another table. There are key values in both tables that match. I have worked in SAS and a little in Python for data analysis, but I am new to Javascript. I am thinking of Javascript as adding the element of time to scripting ("wait to do this-- I'll tell you more about it later!")

A simplification of what I have:

Table1
FieldA- unique record ID- string
FieldB- column to receive info from Table 2- string

Table2
Field1- unique record ID, matches Table1.FieldA-string
Field2- column to populate Table1.FieldB - currently is 'link' field type but could be string if needed

I tried to this via automation but it's not working. My guess is the long strings used as the lookup values are causing the problem, but I really don't know why it's not working, it's very hard to troubleshoot that any further.

I'm pretty close with the script... kind of. I used another script and can get the lookup function to work, and I can get a field to populate. The difference is the original script was written to create a linked field from one table to another. I can't figure out how to modify it to populate an existing field with a link or string field type. I think it may involve changing passing the record IDs as not an array, but there are many for-loops in the script and nothing I've tried has worked.

Here's the script:

//Table with missing values that we want to populate

let mainTable = base.getTable("File Name Link table");

let mainTableRecords = await mainTable.selectRecordsAsync({fields:["NameLink"]});

//Table missing values should come from

let lookupTable = base.getTable("File Links from Google");

let lookupRangeRecords = await lookupTable.selectRecordsAsync({fields:["NameLink2"]});

//Define the value to lookup (lookupValue) to be used in a later step

for (let record of mainTableRecords.records) {  

     let lookupValue = record.getCellValue("NameLink");

    //Find records with matching values, create reference of record IDs

    for (let rangeRecord of lookupRangeRecords.records) {

        if (rangeRecord.getCellValue("NameLink2") === lookupValue) {

            let linkID = rangeRecord.id;

           //Update matching records

            await mainTable.updateRecordAsync(record, {

                test: [{id: linkID}]

            });

       }

    }

}

I want "test" to be "Google Drive Link" and I want it to populate with a field named "link"
Do I just need to just change the last "let" step? Do I need to add another "for" in there?

I've tried adding this to associate the field I want to retrieve with a variable but just haven't gotten anywhere with what I've tried:
let lookupRangeReturn = await lookupTable.selectRecordsAsync({fields:["link"]});


PS- Many thanks to the author of the script used above, which I've used a few times now and has been tremendously helpful, and which came from this post:
https://community.airtable.com/t5/automations/script-to-link-records-if-field-from-table-a-matches-f...

[April_Urban · Accepted solution]

Found another (similar) script and a nice walk through, linked at the bottom of this post. Copy-pasting the script that worked for me directly below.

//Substitute "Orders" for table name which contains values

//on which you want to run the vlookup

let mainTable = base.getTable("File Name Link table");

let mainTableRecords = await mainTable.selectRecordsAsync({fields:["NameLink"]});

//Substitute "Product" for table which contains range to search in

let lookupTable = base.getTable("File Links from Google");

let lookupRangeRecords = await lookupTable.selectRecordsAsync({fields:["NameLink2", "link as text"]});

//Replace "Item.barcode" with column name which has the values you want to look up 

for (let record of mainTableRecords.records) {  

     let lookupValue = record.getCellValue("NameLink");

    //Replace "Barcode" with column name which is the range to search in

    //Replace "Name" with columnn name which value should be returned

    for (let rangeRecord of lookupRangeRecords.records) {

        if (rangeRecord.getCellValue("NameLink2") === lookupValue) {

            let returnValue = rangeRecord.getCellValue("link as text");

            //Replace "Proper Name" with column name from mainTable which should contain the link

            await mainTable.updateRecordAsync(record, {

                "Google drive link as text": returnValue

            });

       }

    }

}

Thanks to this: https://community.airtable.com/t5/development-apis/script-lookup-with-multiple-criteria-including-a-...


That led me to this: https://business-automated.medium.com/vlookup-in-airtable-for-tables-without-existing-links-d8ddf379...