FileMaker 11 introduced many new features that are sure to keep developers busy exploring the possibilities of the product. It is important, however, to understand the ways in which each feature is intended to be used and how. In this post, I’m going to focus on one new feature in FileMaker 11 – filtered portals.
If you have spent any time with filtered portals, you probably have noticed that you do not get the expected results when you try to aggregate portal records. This is due to the fact that even though the filter is applied, it is applied in the presentation layer only. Aggregate functions operate at the data layer and thus rely on the underlying relationship when returning a result.
Does this mean that you can’t aggregate portal records when using a FileMaker 11 filtered portal? Absolutely not! There are several ways to aggregate filtered portal records (script triggered process, GetLayoutObjectAttribute () function, etc..) but these methods require a some work to implement. I recently came across a method demonstrated by Chad Novotny that I had not seen before and was amazed with how elegant and easy it was to implement.
Before I introduce this method, let’s look at very common method used to aggregate related records… In this example, I have a portal on an invoice detail layout which shows me the invoice line items and I’ve been asked to create a filter so a user can easily locate line items whose totals fall into a range specified by the user; showing the aggregate total. In previous versions of FileMaker, I would create the filter mechanism using a couple of global fields and a multi-predicate relationship. I would then create a new calculation field in my invoice table that has the following formula:
Sum ( inv_LI__Lineitems::Total )This method works well and will give me the total amount of related line items. Since we can filter records using FileMaker 11’s portal filter feature, we no longer need to build the filter into the relationship. As I mentioned however, the calculation shown above will not work correctly when using a filtered portal because the calculation formula relies on the relationship criteria, and not the filter criteria to aggregate the related records.
Now, let’s introduce the method mentioned at the beginning of this post. It’s very simple to implement. All that’s required is a summary field in the line items table and a single row portal based on the filtered TO added to the invoice detail layout which contains the filter criteria (essentially, you duplicate the filtered portal to create the new single row portal used for aggregating your portal records). Since you are taking advantage of a summary field, you eliminate the need for additional fields to aggregate the related records. What I like best about this technique is that it can be used for aggregating portal records any time, whether you’re working around FileMaker 11’s portal filtering behavior or just wanting to aggregate portal records in current or previous versions of FileMaker.
Feel free to download an example that demonstrates the method discussed here.
I was trying to wrap my mind around why I would have to have a summary field in each row of a filtered portal in order to achieve this. Didn’t think about the one line duplicate portal to show the summary field(s).
Thanks so much!
Brilliant, thanks for this. Really helpful.
Say I have two of these portals, both with a aggregating Sum function on one layout. Would it be possible to compare these totals by a calculation?
One of the portals shows my expenses, the other my incoming invoices. I’d like to know the difference between these two totals.
Please help me!
Thanks!
@ Alexander,
You’d have to create an unstored calculation in the related table which subtracts the summary fields of expenses and invoices. Make sure you base the calculation from the ‘context of’ the related table occurrence.
Thank you very much for this! I have been looking for a simple way to do this for quite some time.