Portal Filtering With GetLayoutObjectAttribute

By Jon Jeffery.

Fully Exploiting Portal Filtering With GetLayoutObjectAttribute

Each FileMaker portal is based on a relationship, but you can further restrict the records displayed by defining a calculation in the Portal Setup. The filter can be fixed (for example, only showing records created after a certain date or by a certain user). Still, a common use case involves a dynamic filter (for example, the user types a filter term in a dialogue box, and the displayed records are filtered accordingly). Dynamic filters are easy to implement and can be very responsive, allowing rapid homing-in on a required set of records.

The Filter portal records option
The Filter portal records option
A calculation to filter the portal based on a person’s first and last names
A calculation to filter the portal based on a person’s first and last names

…But how do you use the filtered records?

One problem that beginners often face is how to access the selected set of records once they have been filtered. As an example, let’s look at the found count. It is easy to display the number of records in an unfiltered portal; one common way is to define an unstored calculation field in the local table that uses the Count function to tot-up a field value in the related table.

Using the Count function in the local table
Using the Count function in the local table

In fact, this is pretty inefficient, as the function “Returns the number of valid, non-blank values in a field”—that is, the Count function has to evaluate the entry for each record to check that it is valid (numeric, not empty) before it adds it to the final total. Large, hosted files can introduce a noticeable lag when refreshing the portal. A much more efficient alternative is to define an unstored calculation field in the related table that uses the get(FoundCount) function. This is always extremely quick, and if viewed across a relationship, it shows the number of related records (or portal rows).

Using the get( FoundCount ) function in the related record.
Using the get( FoundCount ) function in the related record.

The problem is that both of these methods work across the underlying relationship; they ignore portal filtering. In this example, the filter has reduced the portal to just five rows, but the related record count remains at the full 21,972:

Incorrect count for a filtered portal when using a calculation
Incorrect count for a filtered portal when using a calculation

Similarly, if you used Go To Related Records, you would end up with a found set of 21,972 records, rather than the five that you wanted.

A hard-working alternative — using ExecuteSQL.

One way around this problem is to base the portal on a relationship that uses a global field in the local table as the match field for the related records. We can then run a script that takes the filter value, builds an appropriate SQL statement, executes it using the ExecuteSQL function, and places the recovered set of IDs in the global field, allowing the portal to display them:

Script to filter portal records using ExecuteSQL

This can be incredibly quick (fast enough for a filter-as-you-type triggered by onObjectModify), and, of course, the relationship only ever has the ‘filtered’ records, so that get(FoundCount) and Go To Related Records work perfectly.

On the downside, it requires you to be really confident with SQL. I’ve seen similar methods using the Execute Data API script step and even utilizing Quick Find, but they can all be difficult to ensure optimal performance and can be tricky to maintain—they should all be filed under the heading of ‘Advanced FileMaker Techniques’!

A simple alternative—GetLayoutObjectAttribute to the rescue!

Earlier, I mentioned that a remote unstored calculation using the get(FoundCount) function will act appropriately if viewed across a relationship—it shows the number of related records through the given relationship. In fact, it goes one step further. If we move the field onto the portal, it shows the correct value even after the portal has been filtered:

Correct found count for a filtered portal when using a calculation on each row.
The correct found count for a filtered portal when using a calculation on each row.

This is great, except that we probably want the found count to be displayed above the portal, not on each row. Fortunately, we can access this value using the GetLayoutObjectAttribute function. We just have to use the Inspector to give the field an object name and then define an unstored calculation (in a local field or even in a button bar) that has GetLayoutObjectAttribute (“myObjectName”; “content”).

Giving the object a name
Giving the object a name

This gets the value in the field in the context of the portal, that is, after filtering has been applied. Although it’s necessary to have the field in the portal, this doesn’t have to be visible to users; you can use conditional hiding, and the calculation will still work. I usually give it distinctive formatting to make it obvious to other developers that it’s a special field.

Hiding the object
Hiding the object
Making it obvious to other developers that it’s a special field
Making it obvious to other developers that it’s a special field

We can use the same technique to gather other data from the filtered set of records. If you define a summary field in the remote table using the List option, you can get a list of the record IDs. Add the field to the portal, and you can leverage it to go to related records (for example, using a global field and relationship similar to that described above, or simply using the list to script a Perform Find).

An ID summary field
An ID summary field
Going to related records
Going to related records

This technique is simple to implement, simple to maintain, and gives you full access to the filtered set of data.

The attached demo file demonstrates both this technique and the ExecuteSQL technique.