Working with related tables > Working with related data in portals
 

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 Advanced 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 Advanced 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 master-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