Automation script to link record in separate table - need to batch query

[Ryan_King]

Hello,

I am working on various automations for my use case, but don’t have previous experience with json or the airtable api. I’ve created a multi-step automation that relies on a couple of scripts to link fields in various tables.

I am sure there is a cleaner way to do it, but overall it works when individual records are created.

However, I have realized that when I batch upload a group of new records (as will be a likely use case moving forward) I run into a script error stating that the query for selectrecords has exceeded the max of 30.

I would love some help figuring out how to modify the following script.

I have two tables: Contacts; Companies

When a new record is found in Contacts, it is supposed to search Companies and then create a link automatically if a matching record is found.

I modified a script I found elsewhere in the community. Here is what I am currently using.

//Define the table and query
let cmpTbl = base.getTable(“Companies”);
let cmpQuery = await cmpTbl.selectRecordsAsync();

//Loop through the records and find the Contact ID
for (let record of cmpQuery.records) {
let cmpid = record.getCellValue(“Contact ID”);

//Define linked table and query
let cntTbl = base.getTable("Contacts");
let cntQuery = await cntTbl.selectRecordsAsync();

//Loop through linked table and match ID values
for (let cntRecord of cntQuery.records) {
    if (cntRecord.getCellValue("Contact ID") === cmpid) {
        let inputid = cntRecord.id;
 
        //Update field
        cmpTbl.updateRecordAsync(record, {
            'Import ID': [{id: inputid}]
        });
   }
}

}

[Justin_Barrett · Accepted solution]

Welcome to the community, @Ryan_King! :grinning_face_with_big_eyes: The problem as I see it is that you’re querying the [Contacts] table once for each company in your [Companies] table. My suggestion is to pull the [Contacts] table query code outside of the [Companies] table loop. That means you’re only querying each table once before you do the nested loop through both lists.

While this doesn’t affect the core issue, you can also simplify the link-building process. Right now you’re assigning the contact ID to a variable before building the link, which is an extra unnecessary step.

With both of these changes applied, your code now looks like this:

//Define the company table and query
let cmpTbl = base.getTable("Companies");
let cmpQuery = await cmpTbl.selectRecordsAsync();

//Define contact table and query
let cntTbl = base.getTable("Contacts");
let cntQuery = await cntTbl.selectRecordsAsync();

//Loop through the records and find the Contact ID
for (let record of cmpQuery.records) {
    let cmpid = record.getCellValue("Contact ID");

    //Loop through linked table and match ID values
    for (let cntRecord of cntQuery.records) {
        if (cntRecord.getCellValue("Contact ID") === cmpid) {
            //Update field
            cmpTbl.updateRecordAsync(record, {
                'Import ID': [{id: cntRecord.id}]
            });
        }
    }
}

[Ryan_King]

Thank you! Did the trick!

I definitely have some studying up to do.