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

formulasum Total,Amount

This example calculates the total amount of all records that are more than 30 days old.

formulasum OverDueTotal,Amount,Date<today()-30

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

formulasum OverDueTotal,1,Date<today()-30

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

formulasum OverDueTotal,1,Date<today()-30,"Invoices"

The previous examples have all summed selected (visible) records only. This next example sums ALL overdue invoices, whether they are selected or not.

formulasum OverDueTotal,1,Date<today()-30,"Invoices",true()

Referencing Fields in the Original Database

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.

See Also


10.0UpdatedCarried 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.