Skip to main content

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': I{id: inputid}]


        });


   }


}


}

Welcome to the community, @Ryan_King! :grinning_face_with_big_eyes: The problem as I see it is that you’re querying the eContacts] table once for each company in your eCompanies] table. My suggestion is to pull the eContacts] table query code outside of the eCompanies] 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': r{id: cntRecord.id}]


            });


        }


    }


}

Welcome to the community, @Ryan_King! :grinning_face_with_big_eyes: The problem as I see it is that you’re querying the eContacts] table once for each company in your eCompanies] table. My suggestion is to pull the eContacts] table query code outside of the eCompanies] 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': r{id: cntRecord.id}]


            });


        }


    }


}

Thank you! Did the trick!



I definitely have some studying up to do.

Reply


Terms & Conditions