One of the great features introduced in FileMaker Pro 11 is Filtered Portals. This is the ability to add an extra criteria to the portal that allows you to limit the records that are displayed.
This can be very handy and is useful to consider when designing your graph. Generally speaking there can be lots of ways of reducing the complexity of a Relationship Graph by using filtered portals if you think the design through carefully. I used filters in BaseElements a lot as it’s often showing four or five versions of the same set of related records for difference purposes.
But the other big thing to consider with filters is that they’re part of the User Interface, not part of the Data Model. So things like a Sum of related records inside a calculated field doesn’t include the filter. If you sum a filtered portal you get every record in the relationship, not the filtered records from the portal.
So is there a way to get a sum of the filtered records, by just manipulating the UI? ie no scripting ( triggers included ). Yes there is, and you take advantage of the fact that the portal filter calc has to walk the portal records first.
What you do is use a global variable as the total that you’re displaying at the bottom of the portal, so something like :
Then you modify this within the portal filter calculation itself :
Case ( Portal::ConditionIsTrue ; Let ( $$Total = $$Total + Portal::NumberField ; 1 ) )
Easy. But this will have one issue. This field will continually add up all of the records, so when you start it might total to 6 records, and then after a refresh it will be 12 etc etc. We need a way either to figure out that we’re at the first record, so start at zero, or remember each time after we’ve completed that next time we start again.
Knowing which record you’re on in the portal filter isn’t possible. A function like Get ( RecordNumber) doesn’t return the right value and the portal isn’t active so you can’t use ActivePortalRowNumber. So you need to look at resetting the total after it completes. Portal filters operate before the layout draws, so you can use the fact that conditional formatting is part of the layout drawing process to mark the total as finished. What you can do instead is set a second global variable to True using conditional formatting, and check for this in the portal filter calc.
So, I added a conditional formatting to the total text of :
Let ( $Total = "" ; "" )
The beauty of this is that it doesn’t have to actually have any applied formatting just the calculation itself as one of the conditions. In my case this variable is a local variable of the same name as the global variable that holds the actual total.
Then the portal filter gets modified to
Case ( Portal::ConditionIsTrue ;
Let ( [ $$Total = Case ( IsEmpty ( $Total ) ; 0 ; $$Total ) + Portal::NumberField ;
$Total = True ] ; True ) )
This does : Check if the portal conditions are True. If so, then is our trigger ( $Total ) empty? If so start at zero, otherwise add the current record to $$Total. Then the last line just sets $Total to True so we won’t be starting at zero next time, and also confirms the portal conditions to show this record.
A simple example :
Of course you don’t have to be doing a sum here, you could do any sort of complex calculations, and it’s all done outside of the graph and without any scripting.
One thing I wouldn’t do when implementing this : I wouldn’t use a global or local variable called “Total”. You’re very likely to use that somewhere else, ie in a script or other calc and step on the toes of what you’re doing here. Have a good naming convention for layout variables and use it wisely 🙂