Issues With a script - linking tables works only partially?

[Mariusz_S]

I have two tables: Contacts and Companies
Multiple Contacts can be linked to Company record.
Both share the same “Application ID” Field.

For some reason when I run the script only handful of records are linked.
The linked field is the “Company” field in the “Contacts” table.

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

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

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

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

[JonathanBowen]

Hi @Mariusz_S - from your description, your set-up is like this, yes?

And you want to end up like this:

Your script works 100% on my base, so this suggests that there are some differences in the matching Application ID values, i.e. they look the same but maybe are different (space at the end for example). Or maybe there are some Applications IDs that are in one table but not in the other.

As an aside, the naming here is a bit confusing:

let cmpTbl = base.getTable("Contacts");
let cntTbl = base.getTable("Companies");

Feels like the identifiers should be the other way around, but this isn’t why the script isn’t working.

[Mariusz_S]

Hi @JonathanBowen,

yes that’s exactly how the base is structured.
There’s like 60 records that remain unmatched.

The behaviour is odd as it clearly was able to find a match for one contact but not the other?

[JonathanBowen]

Yes, it looks like it should match, but clearly it isn’t doing that. This is where I think they might be subtly different, e.g. one has an additional space at the start or end - sometimes it is hard to spot this in the UI.

What you can do is, on your inner loop remove the if statement and just replace with:

console.log(cntRecord.getCellValue("Application ID"), cmpid)

so you’re not testing for equality here, but just logging out the two values - I find that this sometimes highlights data issues.