Sum Up Subform Values

From the Inside Sesame Help Desk

I'd like to have a field on my Client form that shows a current balance of all of the Billings I've sent to and Payments I've received from that client. In my Clients form I have a subform named Transactions with the following fields:

Date (date of the transaction)
Description (type of transaction - bill, payment, credit, etc)
Amount (positive or negative dollar amount - bill is positive, payment is negative)

I've tried looping through the subform, like the example in the sample Charity Donations application, and I just can't get it right. There must be a simpler way.
Billie

There are two much easier ways.

First there's the FormDependentValue() function. It monitors the state of a data element on a specified subform. If either the field or the form change, it runs the operation you specify and puts the result in the specified element on the parent form.

The allowed operations are:

Sum (0), Average (1), High (2), Low (3), Concatenate (4), Count (5).

If you had an element on your main form named Balance and you placed the following statement in the Global Code section of the main form, you'd always see the balance of your client's account (the sum of all of the individual amounts in all of the subforms):

FormDependentValue(Balance, "Transactions", "Amount", 0)

One line of code. It can't be much easier than that.

Next is the combined use of two Sesame functions - @FormGetValues() and @SumListValues().

@FormGetValues() returns a string array of the values of the specified element for every instance of the specified form in its current result set. @SumListValues() takes a string array of numeric values as an argument and returns the sum of those values as a double. You can attach the following statement to a command button or On-Change event of a form or element:

var vCharges as string
var vBalance as double

vCharges = @FormGetValues("Transactions", "Amount")
vBalance = @SumListValues(vCharges)
Balance = vBalance

What happens is that @FormGetValues gets all of the values of the Amount field from all of the Transaction subforms linked to the current main record and puts them in a semicolon delimited string something like this: vCharges = "123;9271.50;81.75;-300.50".

Then @SumListValues(vCharges) sums those string values as a double (number) equaling 9175.75

In this case there are five simple lines of code to get what you want. You can also use other string array functions such as @AverageListValues() or @CountStringArray() with the @FormGetValues() function.

Why would you choose one way over the other? It depends on how much control you want over when the calculation takes place. FormDependentValue() is 100% automatic and will give you a result of 0 (zero) when there are no subforms yet filled in. With @FormGetValues() you can control when the calculations are run - for example, if there's a particular value in a field that's greater than zero (> 0).

The choice is yours. Both methods are easy.