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

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

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

Hope this give you some pointers!

JB

2 Likes

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

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
    }
)
1 Like

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