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 related records in a portal.

1. In Layout mode, double-click the 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	Primary key
	Salesperson ID	Text	Foreign key
	Amount	Number
	Total Sales	Summary	Computes the total of the Amount field; see Defining summary fields
Salesperson	Salesperson ID	Text	Primary key
	Name	Text

2. In the relationships graph, create a relationship between the two Salesperson ID fields.
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 

• The portal filtering and sorting options in the Portal Setup dialog box are unavailable for portals that show records from the current table. See Creating portals for list-detail layouts.
• 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.
• For a more complex summary of your data, use a calculation field to define a formula. See Defining calculation fields.

Related topics 

• Creating portals to display related records