How to do a “Grand Total” across all records.

[Dale Peterson]

I just created a very simple DB for my part time woodworking business. It’s basically an “all income sales” DB. I sell several products to several customers. Each of my customer records show what they ordered, how many of each item they ordered, and then calculating their total purchase on one given day. The final income ends up within the “Grand Total” field from their one day purchase.

My question is: I also have a field titled “Total Shop Income” in each of my customer’s records. My request is to show the sum of “all” sales within this field in each record for a total from all records. In other words, I’d like to show a running total income from all customers within this single field within each record. (See attached screen shot.)

Thanks, Dale.

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

Create a new script field, delete all of the boilerplate it creates and then double click on the field you want to total, it should look like this:

var order_cost = record.getFieldValue('fld-acdbf6132877436d8f86d12a7a732a13');

Delete the text before record.getFieldValue and replace it with form.getTotalOfField, it should look like this:

form.getTotalOfField('fld-acdbf6132877436d8f86d12a7a732a13');

The exact text of the fld- stuff will be different based on your actual field.

[Brendan]

Alternatively if you click the ID button on the Script Editor, you’ll get a variable assigned to the Field ID of the selected field. Then you can use that in the getTotalOfField() function.

I prefer to see the Field IDs as separate things because when you look at the code it’s hard to see what field you’re getting the total from.

For example:

var order_cost_id = 'fld-acdbf6132877436d8f86d12a7a732a13';
var order_cost_total = form.getTotalOfField(order_cost_id);

I know it’s two lines instead of one, but it’s easier to read then.

[Dale Peterson]

Thanks, Brendan and Sam. I got it to work, but it’s not showing in the field as currency even though I have that selected as the “Number Format”. Not a big deal.

Thanks again for your wonderful help, Dale.

[Sam Moffatt]

In the script editor, make sure the return type is set to be a number in addition to the number format setting in the field settings. The default for script fields is text.

[Dale Peterson]

Sam, you are my rock star of the day! Thanks so much, Dale.

[Author]

Posts