Problem attempting to populate 1 table with linked record based on matched field(s) in another table

[EKBrang]

Non developer here. I think different flavors of what I’m trying to do have been asked in this forum, but nothing that’s quite a match. I’ve tried employing what I’ve found in the threads with some variations with no luck so far. What I’m trying to do is populate a blank column titled “Agency Talent” in my Agency table with linked records from my Talent Table. Eventually I want the field to be populated when there’s a match on two criteria (Agency match and skill category {e.g. IT, Operations, Engineering, etc.}), but I figured I would try to make it work on one critiera first to understand what scripting block was doing before adding the next layer of matching.

The Agency names are under a column titled “Owner” in that table and are under a column titled “Employer” in the talent table.

It seems that I’m not properly defining the "Name (Last,First)id; that is the name of the first column in the Talent table where I want to pull the linked record from. For some troubleshooting I’ve tried just substituting Employer.id since it is already called out, but all i get when running is an “error, something went wrong.”

Any ideas for where I’m going wrong?

let Agency = base.getTable("Agency");

let Talent = base.getTable("Talent");

let AgencyQuery = await Agency.selectRecordsAsync();

let TalentQuery = await Talent.selectRecordsAsync();

for (let Owner of AgencyQuery.records) {

    for (let Employer of TalentQuery.records) {

        let OwnerName = Owner.getCellValueAsString("Owner Name");

        let EmployerName = Employer.getCellValueAsString("Employer Name");

             if (OwnerName == EmployerName) {

            await Agency.updateRecordAsync(Owner, {

                "Agency Talent": [{id: 

[Name (Last,First).id}]

[EKBrang]

I was able to make the script below work (to a degree). It will update the field with the linked record I want, BUT the problem is it only works for 1 linked record. If I have multiple people who work at each “Agency” it will overwrite them and only populate the field with one linked record. How do I properly tell the code I want it to link multiple records?

// Call out the tables we are will look in for matching fields
let AgencyTbl = base.getTable(“Agency”);
let TalentTbl = base.getTable(“Talent”);
let AgencyQuery = await AgencyTbl.selectRecordsAsync();
let TalentQuery = await TalentTbl.selectRecordsAsync();
//Loop through agency name records in Agency table
for (let Agency of AgencyQuery.records) {
//Loop through agency name records of Talent Table
for (let Owner of TalentQuery.records) {
//Call out column names that we will look in for matches
let AgencyName = Agency.getCellValueAsString(“Owner Name”);
let OwnerName = Owner.getCellValueAsString(“Agency/Owner”);
if (AgencyName == OwnerName) {
//update “Talent” column in Agency table with Talent linked record
await AgencyTbl.updateRecordAsync(Agency,{
“Talent”:[{id: Owner.id}]
})
}
}
}

[Shrey_Banga]

Nice work! Yeah the update to any field value always overwrites the previous value. What you can do is keep track of the original linked record ids and add to them. Something like this should work:

// Call out the tables we are will look in for matching fields
let AgencyTbl = base.getTable("Agency");
let TalentTbl = base.getTable("Talent");
let AgencyQuery = await AgencyTbl.selectRecordsAsync();
let TalentQuery = await TalentTbl.selectRecordsAsync();
//Loop through agency name records in Agency table
for (let Agency of AgencyQuery.records) {
    let AgencyTalent = Agency.getCellValue("Talent") || [];
    //Loop through agency name records of Talent Table
    for (let Owner of TalentQuery.records) {
        //Call out column names that we will look in for matches
        let AgencyName = Agency.getCellValueAsString("Owner Name");
        let OwnerName = Owner.getCellValueAsString("Agency / Owner");
        if (AgencyName == OwnerName) {
            AgencyTalent.push({ id: Owner.id });
        }
    }
    //update “Talent” column in Agency table with Talent linked records
    await AgencyTbl.updateRecordAsync(Agency, { "Talent": AgencyTalent })
}

[EKBrang]

@Shrey_Banga, awesome, thanks! Now i’ve ran into the next hurdle. The script will populate for some of the records, but will almost seem “stuck” running with nothing happening. I have roughly 300 “Agencies” in the Agency table, and currently only about 30 people in my “Talent” table. Roughly half of them get linked before any more will populate in the Agency Table. Is the issue that I’m having it look through too many records at once?

[Shrey_Banga]

Yeah you might be running into a rate limit. Though when that happens, your script should show an error message and stop running. If it finishes silently, there might be a different issue.
In both cases, I would guess that the most likely problem is a missing await in front of a function like updateRecordAsync. This causes your script to not wait for the update to finish, so you can end up running too many updates at once, triggering the rate limit or your script stops running before the updates have actually finished, causing only some updates to be applied.

[Tom_Glatt]

I have a question about this code since it seems it may solve a similar challenge for me. Rather than update a linked record field I want to update two fields in an unrelated table. When I tried to change the code I ran into an issue due to the field type mismatch. How should the code be re-written? I assume the changes are in the bottom two lines of code but every effort I make returns some kind error.