Link Record by Email

[Jen_Rudd]

Hi There! I am just trying to jump in as we are working on data migration for a client. I need to be able to take a table (“Job Applications”) and search for a record in another table (“Applicants”) using a field (“Email Address”) as the lookup. Once the record is found, I would like to link the (“Applicants”) record to the (“Job Applications”) record. I have 10,000 records to scrub, so my current zap is laughable.

Thanks for your help!

[Bill_French]

Grow_With_Jen:

I have 10,000 records to scrub, so my current zap is laughable.

There are many reasons to laugh about some things that people try with Zapier.

I predict 102 lines of script in a block would do it.

[Jen_Rudd]

Here’s the code I came up with!

//Define the table and query
let jobTbl = base.getTable(“Destination Base”);
let appQuery = await jobTbl.selectRecordsAsync();

//Loop through the records and find the Applicant
for (let record of appQuery.records) {
let appname = record.getCellValue(“Matching Term”);

//Define linked table and query
let applicantTbl = base.getTable("Other Dataset");
let applicantQuery = await applicantTbl.selectRecordsAsync();

//Loop through linked table and match ID values
for (let appRecord of applicantQuery.records) {
    if (appRecord.getCellValue("Matching Term") === appname) {
        let inputid = appRecord.id;
 
        //Update field
        jobTbl.updateRecordAsync(record, {
            Field To Link: [{id: inputid}]
        });
   }
}

}

It works well, wanted to share and it saves hours of life.

[Mike_Pennisi]

@Grow_With_Jen there’s one part of your script that could cause problems when there are many records: the call to updateRecordAsync. The update does not take place instantly (that’s why the method has Async in its name), so when it is called in a loop, your script may make many requests to update simultaneously.

Airtable limits the number of concurrent requests you can make, so it’s possible that this will exceed the limit and result in errors.

Using the await operator will make sure that each individual update completes before the next update is started.

Here’s how to make the change:

 //Loop through linked table and match ID values
 for (let appRecord of applicantQuery.records) {
     if (appRecord.getCellValue("Matching Term") === appname) {
         let inputid = appRecord.id;
 
         //Update field
-        jobTbl.updateRecordAsync(record, {
+        await jobTbl.updateRecordAsync(record, {
             "Field To Link": [{id: inputid}]
         });
    }
 }

[Jen_Rudd]

Thanks, Mike. Very helpful, I do notice it slogs down the workspace when it is running. It is used mostly for initial database transfer, but will be helpful in general!