The formulasum statement calculates a total by scanning a database, returning the accumulated sum of evaluating a formula over and over again for each scanned record.
This statement has five parameters:
result – is the name of the field or variable that will receive the final sum.
sumformula – calculates the value that will be accumulated for each scanned record.
queryformula – is an optional formula that determines whether a record should be included in the accumulated sum. For example, the query formula
Price > 100 could be used if you only wanted to include items with a price greater than 100 in the total. The query parameter can be omitted, in which case all records will be included.
database – is the database that will be scanned. This database must be currently open. If this parameter is
"", or if the parameter is omitted entirely, the current database will be scanned.
includeallrecords – specified whether all records should be scanned. If this parameter is omitted, or if it is false, only selected (visible) records will be included in the total. If this parameter is true, all records, including invisible records, will be scanned.
This statement evaluates the formula for each record in the specified database, accumulating a total on a record by record basis.
This example sums the numeric field Amount (in the current database) and writes the answer to Total (a variable or a field in the current database).
This example calculates the total amount of all records that are more than 30 days old.
This example counts the number of records that are more than 30 days old. It does this by adding 1 for each overdue record (the previous example added the amount for each overdue record).
This example counts the number of invoices that are more than 30 days old. The Invoice database doesn’t have to be the currently active database (but it does have to be open).
The previous examples have all summed selected (visible) records only. This next example sums ALL overdue invoices, whether they are selected or not.
Sometimes you may want to reference fields in the current database in the query formula. For example, suppose you have an Invoice database that contains customer names in a Name field, and a Customer database that contains First and Last name fields. This formula can be used in the Customer database to calculate the total of all invoices for the person currently selected in the Customer database.
formulasum CustomerTotal,Total,Name = ««First»»+" "+««Last»»,"Invoices",true()
Notice that the First and Last fields are enclosed in double chevrons. The use of double chevrons specifies that a field is in the current database, instead of the database that is being scanned. There are three fields referenced in this formula:
Name ☞ in Invoice database First ☞ in Customer database (because of double chevrons) Last ☞ in Customer database (because of double chevrons)
Advanced Note: Internally, Panorama actually converts double chevrons into the fieldvalue( function. So the example above is internally converted into this:
formulasum CustomerTotal,Total,Name = fieldvalue("Customer",First)+" "+fieldvalue("Customer",Last),"Invoices",true()
The double chevron notation is a convenience, making this formula much easier to write, and to understand.
|10.0||Updated||Carried over from Panorama 6.0, but now has optional additional parameters for specifying a subset of records to be scanned, specifying an alternate database, and specifying whether invisible records should be included in the total.|