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.