Scripting: Automatically Linking based on multiple select field

Hi folks,

I have two tables and am trying to automatically link them with a script.

In this example people apply via an airtable form with a text based “parter code” field. There is a partners table where each partner has a multiple select field that contains all of their partner codes.

Table 1: Applicants
Field: Partner Code (text)
Field: Referring Partner (linked field)

Table 2: Partners
Field: Partner Codes (multiple select)
Field: Applicants using Partner Code (linked field)

Here is what I have so far, based on modifying the scripts of others:

//Define the Partners table and query
let partnersTbl = base.getTable("Partners");
let partnersQuery = await partnersTbl.selectRecordsAsync();

//Define Applicants table and query
let applicantsTbl = base.getTable("Applicants");
let applicantsQuery = await applicantsTbl.selectRecordsAsync();

//Loop through the records and find the Partner Code
for (let record of partnersQuery.records) {
    let partnercode = record.getCellValue("Partner Code");

    //Loop through linked table and match Partner Code values
    for (let applicantRecord of applicantQuery.records) {
        if (applicantRecord.getCellValue("Partner Code") === partnercode) {
            //Update field
            partnersTbl.updateRecordAsync(record, {
                'Applicants using Partner Code': [{id: applicantRecord.id}]
            });
        }
    }
}

Also, does this script try and match the entire table every time the script is fired?
Right now this script is timing out. Any help would be greatly appreciated.
Thanks,
Graham

@Justin_Barrett I think you’ve done something like this in the past. I based this off of your script; thank you!

That does look familiar, but I won’t have time to dig into this until this weekend. If anyone else wants to jump in, feel free.

Sorry for the delay. Between multiple migraines and holiday activities, I’ve been a bit busy.

Like I said before, this looks somewhat familiar, but maybe because it’s a fairly generic (not in a bad way) way of finding matches between tables and making links based on the results. There are a couple of issues with how this script is designed, but before getting too far into that, I want to address this question:

Yes. It’s designed to look at all partners and match them against all relevant applicants. Based on your question, it sounds like you’d prefer to only run the script on new applicant records submitted from the form, not all applicant records. Is that correct? If so, this could be set up via an automation.