Database Reference
In-Depth Information
the robust relational database it is now, the
GetSummary()
function was the best way to sort
and summarize certain kinds of data. Now that FileMaker is relational, you usually use cal-
culations through table occurrences to do that work.
Repeating Functions
Repeating
functions work with repeating fields, and some of them work with
related
fields
as well. You can make nonrepeating fields and repeating fields work together properly in cal-
culations, access specific repeating values, or get the
last
non-empty value. Since repeating
fields have limited uses in these days of related tables within files, so do these functions.
UP TO SPEED: AGGREGATE FUNCTIONS
Aggregate functions work on groups of things. They can work on multiple fields within a record, a
group of related records, or even multiple functions. The Sum function helps you add up the various
charges on an invoice, using fields on the Invoice layout:
Sum ( Subtotal ; Sales Tax ; Shipping )
But since you can use an operator to get the same result (
Subtotal + Sales Tax + Shipping
),
this type of use isn't very common. More often an aggregate function refers to a related field. In that
case, FileMaker aggregates that field's values from
every
related record:
Sum ( Line Items::Extended Price )
An aggregate function can also reference a single repeating field, either a local one or a related one.
As with a reference to a related field, a Sum function that refers to a repeating field adds the values
in every repetition into a single value.
This special behavior for related or repeating fields works only if you use a single parameter. You
can't, for example, sum two sets of related fields as one like this:
Sum ( Line Items::Extended Price ; Line
Items::Shipping Charge )
If you refer to more than one field in a sum function, then it looks at only the
first
related value or
repetition for each field. Of course, if you do want to total two related fields, you can do so by call-
ing Sum twice and adding their results:
Sum ( Line Items::Extended Price ) +
Sum ( Line Items::Shipping Charge )
Financial Functions
Financial
functions make the MBAs in the audience feel right at home. Calculate present
value, future value, net present value, and payments. Non-MBAs could calculate the cost of
competing loans with these functions.
