User manual

Important To generate subsummary values, you must sort the
database by the break field.
Syntax Definition
GetSummary (summary field, Returns the subsummary value of summary
break field) field for the current group of records when
records are sorted by break field; otherwise,
returns an empty result.
GetSummary (summary field,
summary field)
Returns the grand summary value of summary
field for the current found set of records.
Note An alternate way to summarize data with calculations is to
create a self-join relationship. Use the break field as the match field
on both sides of the relationship (to group related records), then use
aggregate functions to summarize data within each “group.” See
“Summarizing data in portals” on page 8-12.
Repeating functions
Use repeating functions in calculations referencing repeating fields.
Syntax Definition
Extend (non-repeating field) Allows the value in a non-repeating field to be
used in a calculation involving repeating fields.
(See the example below.)
GetRepetition (repeating Returns the contents of the repetition number of
field, number) repeating field.
Last (repeating field) Returns the last valid, non-blank value in
repeating field.
This example shows how the Extend function ensures accurate
results in calculations involving both repeating and non-repeating
fields.
Using formulas and functions 11-9
Item Total Tax Rate Item Tax
Without Extend, a calculation
includes a non-repeating value
5.00
.07 .35
0
0
in the result for only the first
17.00
repetition of the calculated field
82.79
=Item Total * Tax Rate
Item Total Tax Rate Item Tax
Using Extend, a calculation
includes a non-repeating value
5.00
.07 .35
in the result for all repetitions of
17.00 1.19
the calculated field
82.79 5.79
=Item Total * Extend (Tax Rate)
Financial functions
Syntax Definition
FV (payment, interest Returns the future value of an investment based on
rate, periods) interest rate and payment for periods.
NPV (payment, interest Returns net present value of a series of unequal
rate) payments (in a repeating field) made at regular
intervals using a fixed interest rate (in a number
field) per interval. (When payment contains equal
values in each repetition, use the PV function.)
PMT (principal, interest Returns the payment needed to meet the
rate, term) requirements of principal, interest rate, term.
PV (payment, interest Returns the present value of equal payments made
rate, periods) at regular periods, assuming a fixed interest rate
per payment.
Note
• Term is expressed in months
• Interest rate is a value from 0.0 to 1.0, divided by 12 (months).