Modifying automation vlookup script to lookup created record

Question

I’m using a ‘vlookup’ style script below as part of an automation triggered each time a new record is created.
//Substitute "Orders" for table name which contains values
//on which you want to run the vlookup
let mainTable = base.getTable("Litters");
let mainTableRecords = await mainTable.selectRecordsAsync({fields:["breed_string"]});

//Substitute "Product" for table which contains range to search in
let lookupTable = base.getTable("Breed Landing Pages");
let lookupRangeRecords = await lookupTable.selectRecordsAsync({fields:["Name"]});

//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("breed_string");

//Replace "Barcode" with column name which is the range to search in
    for (let rangeRecord of lookupRangeRecords.records) {
        if (rangeRecord.getCellValue("Name") === lookupValue) {
            let linkID = rangeRecord.id;
     
            //Replace "Product" with column name from mainTable which should contain the link
            await mainTable.updateRecordAsync(record, {
                breed_lookup: [{id: linkID}]
            });
       }
    }
}

My aim is to lookup the value just on the record that has been created (rather than all columns as the script currently does) and return a corresponding value from another table.
Can anybody help me in tweaking the code accordingly?

Justin_Barrett · Accepted Answer

Welcome to the community, @Matt_Leaver! :grinning_face_with_big_eyes: First you’ll need to add an input variable to the script action to let you bring in the record ID of the triggering record. I’ll refer to this as recordId going forward.

Once that’s done, add this line to the top of your script to bring in that record ID:

const {recordId} = input.config()

Below the line where you define the table where this record lives, replace the line that retrieves all records with this line (I think I picked the correct table, but double-check to be sure):

const record = await mainTable.selectRecordAsync(recordId, {fields: ["breed_string"]})

Remove the outermost for...of loop structure (the definition line and the closing curly brace), so that the next line after the one above would be the one picking the lookup value from the record (un-indent the rest for clarity as well). The rest should work unmodified.

Amazing, works perfectly - thanks so much @Justin_Barrett .

Matt_Leaver · Answer

Welcome to the community, @Matt_Leaver! :grinning_face_with_big_eyes: First you’ll need to add an input variable to the script action to let you bring in the record ID of the triggering record. I’ll refer to this as recordId going forward.

Once that’s done, add this line to the top of your script to bring in that record ID:

const {recordId} = input.config()

Below the line where you define the table where this record lives, replace the line that retrieves all records with this line (I think I picked the correct table, but double-check to be sure):

const record = await mainTable.selectRecordAsync(recordId, {fields: ["breed_string"]})

Remove the outermost for...of loop structure (the definition line and the closing curly brace), so that the next line after the one above would be the one picking the lookup value from the record (un-indent the rest for clarity as well). The rest should work unmodified.

Amazing, works perfectly - thanks so much @Justin_Barrett .

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded