Script block unable to update linked record value

[Felicity_Evans]

Continuing the discussion from Script Block - Linked Record Issue:

Hi Just wondering if this is the same issue. I have a linked record to another table. I’m trying to update this via a script. If I update a non-linked record it outputs as expected but seems unable to update the linked record. The code is as below:

// set the table
let dashboardTbl = base.getTable("Coaching - meetings");
// get the table records
let dashboarddate = await dashboardTbl.selectRecordsAsync();

// loop through the records
for (let record of dashboarddate.records) {  
    // get date from field value "Date"
    let dashboarddate = record.getCellValue("Date");
    // turn it into a date object
    dashboarddate = new Date(dashboarddate);
    //get year from date
    let dashboardyear = dashboarddate.toLocaleString('en-GB', {year: 'numeric'});
   //get month from date
    let dashboardmonth = dashboarddate.toLocaleString('en-GB', {month: 'long'});
    // only run on records which no linked dashboard data
    if (record.getCellValue('Dashboard') === null) {
        output.text(`Copying date for record ${record.name} ${dashboardyear}${dashboardmonth}`)
        // update the Dashboard value with year followed by month
        dashboardTbl.updateRecordAsync(record, {
            //Test: dashboardyear + dashboardmonth
            Dashboard: dashboardyear + dashboardmonth
        })        
    }
}

[Bill_French]

Felicity_Evans:

Dashboard: dashboardyear + dashboardmonth

Should this element be an array?

Dashboard: [dashboardyear + dashboardmonth]

[Felicity_Evans]

Hi Bill, I want it to read YYYYMonth e.g. 2020March which it does when I output to a text field (the commented out Test in this example). It just does nothing with a linked field but doesn’t throw an error either.

[Bill_French]

Felicity_Evans:

It just does nothing with a linked field but doesn’t throw an error either.

Yep - as I recall, failing to pass the data into a link field as a string [in an array] fails silently.

[Jeremy_Oglesby]

Did you try @Bill.French’s suggestion to try wrapping that string concatenation up in an array [...]?

I’m pretty sure he’s right, that inserting into a Linked Record field requires an array of strings as the input, not a single string. Even if you are only inserting a single Linked Record, that solitary Linked record string still needs to be inside an array.

[Shrey_Banga]

Yeah the format for inserting linked records is updateRecordAsync(record, {fieldName: [{id: linkedRecordId1}, {id: linkedRecordId2}...]}). If you search for multipleRecordLinks in the docs you can find the format and an example.

[Felicity_Evans]

Thanks for the head up Shrey. I tried an array as mentioned above but this didn’t work.

So I have to find the linked record ID? I’m unable to derive this from the name. Frustrating as it means I my script is unusable.

[Jeremy_Oglesby]

@Felicity_Evans,

I think you could get the id you need by querying the table you are linking to, and matching the record by the primary field against that YYYYMonth output you are creating (this only works if that name is unique per record in the Linked table, though).

let otherTable = base.getTable("Other table");
let otherTableQuery = await otherTable.selectRecordsAsync();

for (let otherRecord of otherTableQuery.records) {
    if (otherRecord.getCellValue("Name") === dashboardyear + dashboardmonth) {
        let idYouNeed = otherRecord.id;
    };
};

...

dashboardTbl.updateRecordsAsync(record, {
    "Dashboard": [{id: idYouNeed}]
});

It’s a bit more work, for sure, but I don’t think your script is unusable if you can manage to fit something like this in.

[Felicity_Evans]

Thanks all for your help. I managed to get this ti work. My JS skills are rusty at best so I really appreciate the assistance. If anyone trying something similar here is my code which may be of help for you also if you are trying to derive a linked field from data already entered in a record:

// set the table
let dashboardTbl = base.getTable("Coaching - meetings");
// get the table records
let dashboarddate = await dashboardTbl.selectRecordsAsync();

// loop through the records
for (let record of dashboarddate.records) {  
    // get date from field value "Date"
    let dashboarddate = record.getCellValue("Date");
    // turn it into a date object
    dashboarddate = new Date(dashboarddate);
    //get year from date
    let dashboardyear = dashboarddate.toLocaleString('en-GB', {year: 'numeric'});
    //get month from date
    let dashboardmonth = dashboarddate.toLocaleString('en-GB', {month: 'long'});
    // only run on records where linked "Dashboard" field is empty
    if (record.getCellValue('Dashboard') === null) {
        //message output - fields that will be updated
        output.text(`Copying date for record ${record.name} ${dashboardyear}${dashboardmonth}`);

        //Linked table
        let otherTable = base.getTable("Dashboard");

        let otherTableQuery = await otherTable.selectRecordsAsync();

        //Loop through linked table
        for (let otherRecord of otherTableQuery.records) {

            //match linked cell value to string specified
            if (otherRecord.getCellValue("Reporting month") === dashboardyear + dashboardmonth) {

                //get ID of linked field
                let idYouNeed = otherRecord.id;
         
                //Update original table field with id value of linked field (must be an array)
                dashboardTbl.updateRecordAsync(record, {
                    Dashboard: [{id: idYouNeed}]
                });
           }
        }
    }
}

[Bill_French]

Felicity_Evans:

If anyone trying something similar

Felicity, everyone is trying something similar! :slightly_smiling_face: Thanks for sharing and I’m happy to see you got it working.

Crafting code to create links and many other processes often depend on references to record IDs in other tables. And because of this, I tend to simplify the design by eliminating a nested loop like this:

// Loop through linked table
for (let otherRecord of otherTableQuery.records) {
    ...
}

[One way] this can be done efficiently is by creating a JSON index into the records you need to link to. Instead of looking at every record of the linked table for every record of the dashboard table, prep the process by creating a simple JSON index of all records by “Reporting month”. It would look something like this:

// create a json index of linked table
let jReportingMonth = {};
for (let otherRecord of otherTableQuery.records) {
    jReportingMonth[otherRecord.getCellValue("Reporting month")] = otherRecord.id;
}

This simple preamble ( which should execute at the top of your block) makes it possible to scan this link-to table once and thereafter lookup any record ID by simply using the index pointer - example:

dashboardTbl.updateRecordAsync(record, {
  Dashboard: [{id: jReportingMonth[dashboardyear + dashboardmonth].id}]
});

This single line of code (above) replaces the entire nested loop and improves performance by roughly 500ms times the number of nested lookups.

JSON indexes like this can be very handy for many use cases, eliminating a lot of code and script cycles.

Jeremy_Oglesby:

this only works if that name is unique per record in the Linked table

And to @Jeremy_Oglesby’s point, you can quickly see why this is important. In the process of creating the JSON index you are ostensibly creating a unique index based on "Reporting month". If there are multiple identical values, your JSON index will contain the record ID of only the last one in your table. This can cause problems, or in some use cases this is highly desired because it allows you to create aggregations easily.