User manual

Chapter 4 | Working with related tables and files 112
Summarizing data in portals
You can summarize data that’s in a related field displayed in a portal. The portal can contain
records from a related table or from the same table (a self-join).
To summarize data in portals:
1. Create a calculation field in the table in which you want the total to be displayed.
2. For the calculation field, define a formula that includes an aggregate function for the type of
summary calculation you want to perform.
For example, if you are in the Invoices table and want to define a calculation to total the related
values in the Price field of the LineItems table, use the formula Sum(LineItems::Price).
3. Place the calculation field on a layout of the table in which it is defined.
See “Placing and removing fields on a layout” on page 85.
Filtering records in portals
By filtering portal records, you can display different sets of records in a portal.
1. Double-click the portal.
2. In the Portal Setup dialog box, select Filter portal records.
3. In the Specify Calculation dialog box, define a calculation that determines which portal records
will be displayed.
For example, if you are working in an Invoices layout, in a portal that shows product records
from a LineItems table, and want to display just the products with quantities greater than 1, use
the formula If(LineItems::Quantity
< 1; 0; 1).
4. Click OK.
Important The results of summary fields, calculations, and find requests are based on the full set
of related records, not just the records in a portal that are filtered. For example, if a portal is
displaying a filtered subset of records, and a Total of summary field outside the portal summarizes
these records, the summary field will total all related records, not just the displayed records.
For more information about filtering records in portals, see Help.
About lookups
A lookup copies data from another table into a field in the current table. After data is copied, it
becomes part of the current table (and remains in the table from which it was copied). Data copied
to the current table does not change automatically when the data in the other table changes,
unless the data in the match field of the current table changes.
To establish a connection between tables for a lookup, you create a relationship. Then you define
a lookup to copy data from a field in the related table into a field in the current table.
When you type a value in the match field of the current table, FileMaker Pro uses the relationship to
access the first record in the related table whose match field contains a matching value. Next, it
copies the value from the lookup source field into the lookup target field, where the value is stored.