Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

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

1462 4
cancel
Showing results for 
Search instead for 
Did you mean: 

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).
Screenshot 2020-05-12 at 03.02.38
Thanks so much for your feedback on this!
Lubem

4 Replies 4

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

Hope this give you some pointers!

JB

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:Screenshot 2020-05-14 at 03.22.45

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

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