Connecting and updating linked records to tables via script

[Sam_Cederwall]

Hello,

Let me preface this by saying I have close to no experience with coding and java script and only started experimenting with the scripting block in the past week or two.

Thanks to some very talented people and previous forum posts, I was able to create (more like borrow and adapt) a script that links records in one table to another based on fields matching between the tables. This has been incredibly useful for me already in some other tables and bases.

What I desire now is similar but slightly more nuanced, I believe. So here’s my (probably too lengthy) attempt at explaining this as clearly as possible:

I have a table in a base with the purpose of capturing payments and invoices (named Account Summary) of a client. I have another table to generate statements for clients. These specific statements are normally generated twice a month and reach back two months in a clients history. I have a field in the Account Summary table that outputs a date and changes based on a date range of the current month and have another field to concatenate this date field with a client code, thus generating an ID (we’ll call this the Statement ID) that will match with the first field (which we’ll call Customer Statement ID) in the statements table. Similar to what I’ve accomplished with other scripts, I want to match the ID that I generate in the Account Summary table to the Statements table and link the two, this I can accomplish. What I would like to do differently is when the Statement ID changes because the date has changed and I want to link this to a new statement with a script, I don’t want the old links to be removed.

As it is now, this is what my script looks like:

//Define the table and query
let acctTbl = base.getTable("Account Summary");
let acctQuery = await acctTbl.selectRecordsAsync();

//Loop through the records and find the Statement ID
for (let record of acctQuery.records) {  
     let stmntid = record.getCellValue("Statement ID");

    //Define linked table and query
    let stmntTbl = base.getTable("Statements");
    let stmntQuery = await stmntTbl.selectRecordsAsync();

    //Loop through linked table and match ID values
    for (let stmntRecord of stmntQuery.records) {
        if (stmntRecord.getCellValue("Customer Statement ID") === stmntid) {
            let inputid = stmntRecord.id;
     
            //Update field
            acctTbl.updateRecordAsync(record, {
                Statements: [{id: inputid}]
            });
       }
    }
}

This script does well at linking a record to a statement. However, as mentioned above, as soon as the Statement ID changes, and I run the script, the previous links to a statement are moved to the current matched IDs. I would like old links to remain so that we don’t lose previous statement information and simply (maybe not so simply) have new links added to the new statements without removing the old ones.

Hopefully this post is clear enough to understand the gist of what I would like to accomplish. If anyone has ideas on how I could get a script to do what I desire, that would be great.

Also, tips on scripting hygiene, best practices, and critiques are appreciated. I understand there are resources for this but it’s helpful seeing comments specific to what I’m working on if it’s not too much of a hassle.

Thanks for the help, it’s much appreciated. If you have any further questions, please ask, I’m happy to try and clarify what I want from this.

Best,
Sam