Skip to main content

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:i"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:i"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: o{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?

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: e"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.

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: e"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 .

Reply


Terms & Conditions