Filter using Relationship vs. Portal Filter

Questions
1

Suppose you have a table with several fields:

  • PersonID
  • StartDate
  • EndDate
  • Status: Calc field showing "Upcoming", "In Progress", and "Finished" based on StartDate and EndDate compared with Get(CurrentDate)

You have a portal with a self-join based on PersonID to show "all jobs for this person"
Futhermore, you want to allow the portal to be filtered, e.g. "Show: All, In Progress, Finished..."

If you add the filter to the relationship, it won't work unless the Status calc field is stored and indexed. But if the status calc field is stored, then it won't update, so when today becomes tomorrow, and tomorrow is after EndDate, the status will still show "In Progress".

I was thinking about how to solve this, when I rememberd that FileMaker Portals can have their own filter formula.

And, it turns out that filter can work on unstored (unindexed) calculations in a related table.

Naively, I would have thought the two methods would be roughly equivalent, but they are not.

2

you'll also find that the two behaviours have other differences.

Relationships defined with the filter are determined on the server. The server decides which records to send.
Portal filters ( with exceptions ) are determined on the client. That difference means that the entire data set has to be downloaded, and then it is filtered.

8 Likes
3

Ahh yes, yet another feature in FileMaker designed for developers to hang themselves with if not fully understood.

To be honest we very rarely use them - preferring instead to use relationship filtering for the reasons @Malcolm outlined.

The only time I can see benefit for them is if your initial set of records through the relationship being used is itself relatively small, say < 50 records or so. Anything beyond that and you're not only downloading large data from the server, but you're also having to do calculations client-side on those records.

3 Likes
4

@weetbicks, totally agree. Perhaps we should construct a list of those ‘legacy’ features not recommended for use outside of a local network (which would be the spark for some lively conversation).

The other thing not mentioned about using the layout portal filter, is that it is only a display function and all calculations referring to that relationship will still be based on the full data set, not only those displayed.

Kind regards
Andy

6 Likes
5

So far I have been holding my tongue on posting an example where the layout level portal filter is advantageous for performance relative to just using the relationship to filter. I was reluctant to post it, because in general, I completely agree with the sentiments already expressed here, and I did not want to detract from that. But, seeing so many final-looking comments, I figured it probably would not hurt to call out one crumb of exception.

Just as with so many things FileMaker, it involves knowing some of the quirks of the product, and how they can work for or against you.

Here's the scenario where the layout portal filters provided better performance:

I once was working on a system where they asked me to have a look at a portal that was part of a log viewer. The viewer had various filter features, and all such filters were implemented at the relationship level. They had found that the log viewer did not scale -- it was fine at first, but once a large number of log records had been accumulated, the viewer was unusable when its date range filter feature was applied.

The problem was that the relationships used inequality operators ( >, ≥, etc.) , and at that time (v.11), such relationships against a very tall table were dog slow.

The solution was to split the filtering implementation up, and hand off each part to the part of FileMaker that could handle it most efficiently. I reworked the filtering to leave all of the exact-matching filter aspects implemented at the relationship level. Those narrowed down the set of matched records to a large degree. The date range inequality was handled at the layout portal filter level. This arrangement had perfectly acceptable performance, despite having to send records down to the client which would later be filtered out -- simply because we got out of the business of using the ≥ and ≤ in the relationship. There was less penalty to send that data across the network than there was evaluating the inequality relationship.

The above situation was back in v.11. Many things have certainly changed since v.11, but if confronted with the same problem today, I wouldn't rule out trying a split filter approach to see what happens. (In general, I do my best to avoid inequalities in relationships, so I am not even aware as to whether this is still a performance pain point, or if it has improved with time.)

Hope this is an enjoyable crumb of an exception.

3 Likes
6

When I am up against a portal filtered at the layout level, and I need a calc that takes into consideration the filtering, I'll sometimes reach for the following approach, if circumstances allow for it:

I put the value that I need to obtain about the portal into a hidden text object in the portal, and then I will access that in my calculation code via GetLayoutObjectAttribute.

A very simple example:

If I need to know the number of rows in a filtered portal:

  • I add a small named text object to the portal which uses the merge symbol: {{FoundCount}}

  • I get the row count of the (filtered) portal via GetAsNumber( GetLayoutObjectAttribute( "objectname" ; "content" ))

I used to do this with small text objects that were formatted so that they would blend in with the portal background, but a sharp colleague of mine taught me that the object can be hidden with conditional hiding, and this still works.

Another use case that is helpful is if one needs to get a list of IDs of child records from a filtered portal. If you are willing to add a ListOf summary field to the child table, that field can similarly be put into a merge field on the hidden object to return a list of the IDs of the (filtered) portal records. I know that generally speaking, the words "summary field" understandably strike fear into the hearts of many, but I have not had issue with using ListOf in this way.

Of course, if one never uses filtered portals, then none of the above really offers anything helpful. In my case, I run into them from time to time when stepping into other dev's solutions.

HTH.

6 Likes