Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Dec 11, 2020 04:17 PM
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
Solved! Go to Solution.
Dec 16, 2020 04:38 PM
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]
}
});
Dec 14, 2020 03:13 PM
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]
});
}
Dec 15, 2020 12:25 PM
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)
}
Dec 16, 2020 01:49 PM
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!
Dec 16, 2020 04:38 PM
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]
}
});