Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Script to update value from another table based on matching records

Topic Labels: Automations Extensions
Solved
Jump to Solution
2452 1
cancel
Showing results for 
Search instead for 
Did you mean: 
April_Urban
5 - Automation Enthusiast
5 - Automation Enthusiast

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

1 Solution

Accepted Solutions
April_Urban
5 - Automation Enthusiast
5 - Automation Enthusiast

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

See Solution in Thread

1 Reply 1
April_Urban
5 - Automation Enthusiast
5 - Automation Enthusiast

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