Using Script to get field sum and then update it on specific/separate field

[Lubem_Mtile]

Just started using the Scripting block! My team is using Airtable to calculate profit pool for an affiliate program, part of that includes getting the sum of a field (which you can see on the page) update it on another field for all record as total number of that field (in this case sum of ‘# of hoodies’ ) and then updated on another field (‘total hoodies’ ). And then update total number of records from another table in a view (Toal # of Affiliate which is the total records from the Approved affiliate table).

Thanks so much for your feedback on this!
Lubem

[JonathanBowen]

Hi @Lubem_Mtile - I wrote a blog post about a similar idea here:

Airscript – 10 May 20

Aggregating numbers

How to sum and compare a set of records

Hope this give you some pointers!

JB

[Lubem_Mtile]

Hi @JonathanBowen Sorry to come back again. For some reason i cant get the sum of the field in ‘Total Hoodies (calc)’ . Which is now 71 see image:

Here is my script:

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

let total = 0;

for (let record of query.records) {
total = total + record.getCellValue(’# of Hoodies’);
}

for (let record2 of query.records) {
total = record2.getCellValue(’# of Hoodies’)+total;
await table.updateRecordAsync(record2, {
“Total Hoodies (calc)”: total
})
output.text(Total Hoodies (calc) ${record2.name} updated!)
}

Cant seem to see what i’m doing wrong

[JonathanBowen]

Lubem_Mtile:

for (let record2 of query.records) {
total = record2.getCellValue(’# of Hoodies’)+total;
await table.updateRecordAsync(record2, {
“Total Hoodies (calc)”: total
})

Hi @Lubem_Mtile - I think it its this part that is wrong. What is happening is:

• total is 71 from the previous loop
• for each record you get the total, add the number of hoodies to the total
• then update the “Total Hoodies (calc)” field with the new total
  Note that total is now no longer 71, but 71 + 1 (after the first iteration), 72 + 3 (after the 2nd iteration) and so on.

If you want to see 71 is the “Total Hoodies (calc)” field for all records you can just do this:

for (let record2 of query.records) {
    let updatedRecord = await table.updateRecordAsync(record2, {
        "Total Hoodies (calc)": total
    }
)

[Lubem_Mtile]

Works just fine!! Thank you @JonathanBowen i see where the issue was now.