Help

Scripting: Automatically Linking based on multiple select field

Topic Labels: Automations
2952 8
cancel
Showing results for 
Search instead for 
Did you mean: 
Graham_Hunter
4 - Data Explorer
4 - Data Explorer

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

8 Replies 8
Graham_Hunter
4 - Data Explorer
4 - Data Explorer

@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.

Did this ever get solved? I have a similar use case.

@Josh_Cooper Similar use cases often have enough different details that it’s worth starting a new thread. You can link to this thread to indicate that it’s related to your problem, but we’ll need a lot more details about your specific use case to know how to help you.

Sorry about that.

I am wanting to link a synced table, Orders, record with another table, Inventory. Both tables have a Part Number field. Basically, I when a new record shows up on the synced table, Orders, I want the automation to link the record with Inventory record with the corresponding Part Number.

Because the table that will trigger the automation, Orders, is the synced table it is possible that there will be multiple new entries at once.

Let me know what other information is needed and I will be happy to provide it.

Thanks!

@Josh_Cooper Sorry for the delayed response. Unfortunately I’ve been—and continue to be for the foreseeable future—pretty swamped, and I can’t look into this any further. Maybe @kuovonne or @Kamille_Parks or @JonathanBowen or @Jeremy_Oglesby can help?

Hi @Josh_Cooper - you don’t need a script to do this - can be done with an Airtable automation on its own. If this is my Orders table:

Screenshot 2021-05-16 at 11.18.57

I’ve got a part number (coming from the table sync) and I’ve added a linked field “Parts” to the Parts table. Then in automations I’ve set up on “new record created” as the trigger and “update” record as the action.

Screenshot 2021-05-16 at 11.19.05

So, in the update action, the Record ID is the ID of the new record. Then I’m setting the “Parts” field (link) to be the value of the Part Number field.

If a part number comes in that is not already in the Parts table, then it creates it in the Parts table.