Script to Link Records if field from Table A matches field from Table B

[Nick_Robeson]

Hi all!

Looking for some help with a script to use in an automation!

Background Info:
My company offers training classes to our clients and we have a base that houses two tables:

1. Training Master List
This table is the Master List of attendees for all classes throughout the year. Includes fields like Client Name, ClientID, Training Completed Date, and Linked Record to Qualified Clients Table

2. Qualified Clients
This table includes clients that are qualified to be featured on our list of preferred partners. Similar to the first table mentioned, this table includes fields like Client Name, Client ID, and Contact info.

I’m looking to have an automation that runs a script every morning at 10AM. The script needs to loop through the ClientID field in each table looking for matches. If a record from the Training Master List Table has a ClientID that matches a ClientID from the Qualified Clients Table, I would like to link the Qualified Client Record in the Training Master List Table.

I’ve created an example base here with creator permissions. In this example base, John James (ClientID 330) is how I would like the records to appear after the script runs. Reid Hoffman (ClientID 458) is an example I created that has not yet been linked if anyone wants to test it out.

First person to provide a working solution and include their email address will get a $10 Starbucks or Amazon Gift Card! If you don’t want to include your email address, feel free to shoot me a message on LinkedIn :grinning_face_with_big_eyes:

[Greg_F · Accepted solution]

Hi @Nick_Robeson,

I believe you are after a script that looks similar to this:

//Substitute "Orders" for table name which contains values
//on which you want to run the vlookup
let mainTable = base.getTable("Orders");
let mainTableRecords = await mainTable.selectRecordsAsync({fields:["Item.barcode"]});

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

//Creating a hashmap of all lookup values from the lookup table
let lookupTableHashMap = new Map()
for (let rangeRecord of lookupRangeRecords.records) {
    lookupTableHashMap.set(rangeRecord.getCellValue("Barcode"),rangeRecord.id)
    }

//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("Item.barcode");
    let linkId = lookupTableHashMap.get(lookupValue)
    
    if(linkId)
        await mainTable.updateRecordAsync(record, {
            // Replace "Product" with column name from mainTable which should contain the link
            Product: [{id: linkId}]
        })
}

I think it should be straightforward enough to adjust and then set as part of automation running daily. I have made a YouTube video about it on my channel about 2 years ago, but it is using a more brute force double for loop. Above solution should be more memory efficient.