formulasum statement

formulasum

RESULT

SUMFORMULA

QUERYFORMULA

DATABASE

INCLUDEALLRECORDS

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.

Parameters

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.

Description

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

assign -- assigns a value to a field or variable.
assignfield -- performs an assignment, much like an equals sign or the assign statement. However, the *assignfield* statement only performs the assignment to a database field, not to any variable.
assignfieldwithsideeffects -- performs an assignment, much like an equals sign or the assign statement. However, the *assignfieldwithsideeffects* statement only performs the assignment to a database field, not to any variable. After performing the assignment, it will run any side effects associated with the field, including formulas and code associated with the field.
assignfileglobal -- performs an assignment, much like an equals sign or the assign statement. However, the *assignfileglobal* statement only performs the assignment to a fileglobal variable.
assignglobal -- performs an assignment, much like an equals sign or the assign statement. However, the *assignglobal* statement only performs the assignment to a global variable.
assignlocal -- performs an assignment, much like an equals sign or the assign statement. However, the *assignlocal* statement only performs the assignment to a local variable.
assignwindowglobal -- performs an assignment, much like an equals sign or the assign statement. However, the *assignwindowglobal* statement only performs the assignment to a windowglobal variable.
define -- performs an assignment, much like an equals sign or the assign statement. However, the *define* statement only performs the assignment if the variable is currently undefined. If the variable already has a value, the *define* statement leaves it alone. The *define* statement is especially useful for initializing permanent variables.
let -- creates a local variable and assigns a value to it.
letfileglobal -- creates a fileglobal variable and assigns a value to it.
letglobal -- creates a global variable and assigns a value to it.
letpermanent -- creates a permanent variable and assigns a value to it.
letservervariable -- creates a server variable and assigns a value to it.
letwindowglobal -- creates a windowglobal variable and assigns a value to it.
set -- performs an assignment, much like an equals sign or the assign statement. However, the destination of the assignment can be calculated on the fly.
setfield -- performs an assignment, much like an equals sign or the assign statement. However, the destination field of the assignment can be calculated on the fly.
setglobal -- sets a value into a global variable. The name of the global variable is calculated on the fly.
setlocal -- sets a value into a local variable. The name of the local variable is calculated on the fly.
setlocalsfromdictionary -- converts a dictionary into a collection of local variables. The names and values of the local variables will be derived from the dictionary contents.

History

Version	Status	Notes
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.