Making a script that calculates a total field then creates a sum record

[davidcarldesign]

The tutorial by Jonathan Bowen has been incredibly helpful.

So far my script calculates the sum of the “Amount” in the “Monzo Transactions” table.

My next step is to figure out what to add to the script so that the next part of the process will update a record in the “Monzo Account Balance” table which inserts the sum every time the script runs. (either in the “Mozo Balance” field or “Total Amount field”)

If anyone can offer the script that would need to be written to complete this process (or advice)
I would appreciate it.

Thanks

[kuovonne]

Have you looked at the Scripting documentation? You want table.createRecordAsync().

[davidcarldesign]

Firstly I wanted to acknowledge how helpful you have been by sharing your knowledge and videos.

Thanks for the reminder of the docs, it certainly helps to understand a bit further and to clarify what I am trying to do.

In this example, all transactions in the “Monzo Transactions” table are created as records through automation.

As transactions take place, the sum changes in the “Amount” field.

The goal is to use a script to Update the record in the “Total Amount” field available in the “Monzo Account Balance” table - with the sum from the “Amount” field in the “Monzo Transactions” table.

I’ve managed to create a log of the sum with the first part of the script.
The second part creates an error.
I need to figure out how to use updateRecordAsync to update the record with sum but it seems I missing something here. I don’t know what to write to assign the latest sum that was logged to populate the Amount field.

So far I have this:

let table = base.getTable(‘Monzo Transcations’);
let query = await table.selectRecordsAsync();

let total = 0;

for (let record of query.records) {
console.log(‘previous total’, total);
total = total + record.getCellValue(‘Amount’);
console.log(‘new total’, total);
}

// Update a record in the Monzo Account Balance
let table = base.getTable(“Monzo Account Balance”);
let query = await table.selectRecordsAsync({fields: });
let recordId = query.records[0].id;
await table.updateRecordAsync(recordId, {
“Description”: “Amount”,
})
console.log(“Updated a record!”);

I’m close, but not quite there yet. Feel free to chime in.

[kuovonne]

Ah. Your screen capture said you wanted the script to create a record. However, it sounds like you want to update a record. When you update a record, you need to know the record ID of the record to update.

davidcarldesign:

let recordId = query.records[0].id;

The problem with this is you are simply getting the record ID of the first record in the query results, which is probably not the record ID you want.

You can pass in the record ID you want as an input variable. (Read the documentation about input variables in automation scripts–they are different in automations versus scripting extension.)

Another alternative is to use output.set() to have the automation script output the total. Then you can use a regular “Update Record” action to update the record.