Working with related data in portals
When you display related data in a
portal, values from all
related records are displayed, subject to a user’s
access privileges. By contrast, when a related field is outside a portal, the value from only the first related record is displayed.
Sorting portal data
To sort related records in a portal, use the Edit Relationship dialog box. See
Creating and changing relationships. When
Sort records is selected and data in a related field in a portal changes, the portal records are re-sorted whenever the related records are reevaluated. For example, records re-sort when you browse out of and then back into the record, or change the value in the match field. (To better control when the sort order is refreshed, use the
Refresh Portal script step.)
The sort order specified in the Portal Setup dialog box takes precedence over the sort order specified in the Edit Relationship dialog box. If you don’t specify a sort order for either the portal or the relationship, related records are displayed in the portal in their creation order. See
Creating portals to display related records.
Filtering records in portals
You can filter records to display different sets of records in a portal.
2. Select Filter portal records.
3. Define a calculation that evaluates to True for the portal records to display.
For example, you are in an Invoices
layout, in a portal that shows product records from a LineItems table. To display just the products with quantities greater than or equal to one, use the formula
If (LineItems::Quantity < 1; 0; 1).
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 there is a
Total of summary field outside the portal summarizing these records, the summary field will total
all related records, not just the displayed records. To use data from the filtered portal records for a summary field, calculation, or find request, create a new relationship using the same criteria as for the filtered portal records, then use related fields from that relationship for the summary field, calculation, or find request.
Summarizing data in portals
You can summarize data that's in a related field displayed in a portal. For example, you can get the total of all related records.
To do this, place a summary field in the table that related records are being displayed from (the table displayed in the Portal Setup dialog box). Then place the summary field on the layout containing the portal.
Suppose a layout uses a portal to display all sales by each salesperson, and you want to include a summary field that displays the total sales amount.
To summarize data in a portal using this simple example:
1. Create two tables with the following fields:
Table name | Field name | Field type | Comment |
Sales | Transaction ID | Text | |
| Salesperson ID | Text | |
| Amount | Number | |
| Total Sales | Summary | |
Salesperson | Salesperson ID | Text | Primary key |
| Name | Text | |
3. In
Layout mode, add the four fields of the Sales table to the Sales layout (if they haven’t been added automatically). Add the two fields of the Salesperson table to the Salesperson layout.
4. On the Salesperson layout, create a portal that shows related records from the Sales table. Include the following related fields from the Sales table in the portal: Transaction ID, Amount, Total Sales.
You can also place the summary field (Sales::Total Sales) anywhere on the Salesperson layout. See
Placing and removing fields on a layout.
5. Switch to
Browse mode, and add the following records to the Sales table:
Record Number | Field | Data |
1 | Transaction ID | T1 |
| Salesperson ID | S1 |
| Amount | 246.00 |
2 | Transaction ID | T2 |
| Salesperson ID | S2 |
| Amount | 52.75 |
3 | Transaction ID | T3 |
| Salesperson ID | S1 |
| Amount | 10.50 |
4 | Transaction ID | T4 |
| Salesperson ID | S2 |
| Amount | 150.00 |
5 | Transaction ID | T5 |
| Salesperson ID | S1 |
| Amount | 17.80 |
FileMaker Pro fills in the value of the Total Sales field.
6. Switch to the Salesperson layout, and add the following data:
Record Number | Field | Data |
1 | Salesperson ID | S1 |
| Name | Andre Common |
2 | Salesperson ID | S2 |
| Name | Sophie Tang |
FileMaker Pro enters the related values from the Sales table into the rows of the portal, and calculates the total of all sales for that salesperson in the Total Sales summary field.
Notes
•You can display data from a single relationship in multiple portals on the same page, and use a different sort order for each portal.
•Filtering records is for display purposes only, not for security purposes.
•The result of a portal filter calculation would be the same as the result in a calculation field in the related table.
•Although fields in portal records update as data changes, a portal filter is evaluated only when the record is loaded. To update portal filtering after the record is loaded, use the
Refresh Portal script step.
Related topics