Skip to main content
Solved

Script to update value from another table based on matching records

  • August 2, 2023
  • 1 reply
  • 0 views
A

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-field-from/td-p/113984

Best answer by April_Urban

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
            });
       }
    }
View original
CJSmith
1 person likes this
  • CJSmith
    CJSmith
Did this topic help you find an answer to your question?

1 reply

A
  • April_Urban
  • Author
  • New Participant
  • 4 replies
  • Answer
  • August 3, 2023

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

Reply


Most helpful members this week

Terms & Conditions