Script for One to Many Links

[Sam_Becker]

Hello All,

As someone who knows nothing about JavaScript, I am trying to write a script that creates a one-to-many link between two tables. I found some code elsewhere on the forum and have modified it slightly.

I have a “Facilities” table that lists out each facility from which we will be collecting data. I also have a “Results” table that has the results from all data collection at every facility. (This table is quite large and new records are added everyday.) The common link between these two tables is the Facility ID. I would like each Facility to have all their results linked (one-to-many relationship) in the “Facilities” table.

Currently, I am able to run the following code and return a one-to-one link. I’m assuming that the output gets rewritten each times it goes through the FOR loop, resulting in only one output. Is there a way to modify the code to have it output multiple links instead of just one?

Here is the code that I am using:

//Define the Facilities table and query
let facTbl = base.getTable("Facilities");
let facQuery = await facTbl.selectRecordsAsync();

//Define Results table and query
let resTbl = base.getTable("Results");
let resQuery = await resTbl.selectRecordsAsync();

//Loop through the records of Results table
for (let resRecord of resQuery.records) {
    let tpID = resRecord.getCellValue("TruePani ID");
    //Loop through the records of Facilities
    for (let facRecord of facQuery.records) 
        if (facRecord.getCellValue("TruePani ID") === tpID) {
            let sourcesOld = facRecord.getCellValue("Sources");
            facTbl.updateRecordAsync(facRecord, {
                'Sources': [{id: resRecord.id}]
                
            });
        }
    }

Thank you!

Sam

[Sam_Becker]

Okay so I got the code working and figured I would post it in case anyone else was having this problem.

//Define the Facilities table and query
var facTbl = base.getTable("Facilities");
var facQuery = await facTbl.selectRecordsAsync();

//Define Results table and query
var resTbl = base.getTable("Results");
var resQuery = await resTbl.selectRecordsAsync();

//Loop through the records of Facilities
for (let facRecord of facQuery.records) {
    let FacTpID = facRecord.getCellValue("TP ID");
    let Sources = []; 
    //Loop through the records of Results table
    for (let resRecord of resQuery.records) {
        if (resRecord.getCellValue("TP ID") === FacTpID) {
            Sources = [...Sources,{id: resRecord.id}]
        }
    }
    facTbl.updateRecordAsync(facRecord, {
                'Sources': [...Sources]
            });
}

[Justin_Barrett]

Glad to see that you found a solution! It should be noted, though, that there’s a hiccup in your code (both old and new): you aren’t using the await keyword before calling the record update. From my experience, updating individual records without that keyword can cause problems, including records that don’t update (correctly, or at all).

The suggested workflow when you know there will be multiple records to update is to build an array containing all of the updates, then pass them in batches to the updateRecordsAsync() method. Such a change to your code would look something like this:

//Define the Facilities table and query
var facTbl = base.getTable("Facilities");
var facQuery = await facTbl.selectRecordsAsync();

//Define Results table and query
var resTbl = base.getTable("Results");
var resQuery = await resTbl.selectRecordsAsync();

// Create an updates array
let updates = [];

//Loop through the records of Facilities
for (let facRecord of facQuery.records) {
    let FacTpID = facRecord.getCellValue("TP ID");
    let Sources = []; 
    //Loop through the records of Results table
    for (let resRecord of resQuery.records) {
        if (resRecord.getCellValue("TP ID") === FacTpID) {
            Sources = [...Sources,{id: resRecord.id}]
        }
    }
    // Push update into the updates array
    updates.push({
        id: facRecord,
        fields: {
            'Sources': [...Sources]
        }
    });
}

// Update records
while (updates.length > 0) {
    await facTbl.updateRecordsAsync(updates.slice(0, 50));
    updates = updates.slice(50)
}

[Sam_Becker]

Hi Justin, thank you very much for your reply! I noticed the await and push functions in similar scripts for this sort of use, so I’m glad to see how they would apply in this situation.

As I mentioned in my post, I know very little JavaScript. When I run the code that you have written, I get the error “TypeError: Invalid arguments passed to table.updateRecordsAsync(records):
• records[0].id should be a string, not an object” at line 33 (which is the Update Records part). Is there an easy fix for this?

Thanks again!

[Justin_Barrett]

Sorry. I didn’t proof my own tweak well enough. The update portion should look like this:

    updates.push({
        id: facRecord.id,
        fields: {
            'Sources': [...Sources]
        }
    });