User manual

Using formulas and functions 11-9
Important To generate subsummary values, you must sort the
database by the break field.
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.
This example shows how the Extend function ensures accurate
results in calculations involving both repeating and non-repeating
fields.
Financial functions
Note
1 Term is expressed in months
1 Interest rate is a value from 0.0 to 1.0, divided by 12 (months).
Syntax Definition
GetSummary (summary field,
break field)
GetSummary (summary field,
summary field)
Returns the subsummary value of summary
field for the current group of records when
records are sorted by break field; otherwise,
returns an empty result.
Returns the grand summary value of summary
field for the current found set of records.
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
field, number)
Returns the contents of the repetition number of
repeating field.
Last (repeating field) Returns the last valid, non-blank value in
repeating field.
Syntax Definition
FV (payment, interest
rate, periods)
Returns the future value of an investment based on
interest rate and payment for periods.
NPV (payment, interest
rate)
Returns net present value of a series of unequal
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
rate, term)
Returns the payment needed to meet the
requirements of principal, interest rate, term.
PV (payment, interest
rate, periods)
Returns the present value of equal payments made
at regular periods, assuming a fixed interest rate
per payment.
5.00
17.00
82.79
Item Total
.07
Tax Rate
.35
0
0
Item Tax
5.00
17.00
82.79
Item Total
.07
Tax Rate
.35
1.19
5.79
Item Tax
=Item Total * Tax Rate
=Item Total * Extend (Tax Rate)
Without Extend, a calculation
includes a non-repeating value
in the result for only the first
repetition of the calculated field
Using Extend, a calculation
includes a non-repeating value
in the result for all repetitions of
the calculated field