Have you ever wished there were an easy way to summarize the contents of one portal in another portal? Perhaps something along the lines of the “Summary” portal below?
I’ve touched on the possibility of using a portal to produce (or assist in the production of) an on-screen summary report a couple times in the past…
…but today we’re going to look at a fresh approach, and if you wish, you can follow along in today’s demo file, fm11-summary-report-in-a-portal, which uses a filtered portal and therefore requires FileMaker 11 or later.
This demo came about because a client, whom I’d built an Expense tracking database for, called to say his bookkeeper wanted to see an on-screen summary of expenses by account number (the actual field name is “account”; I will use the terms interchangeably).
What I like about this particular method, in contrast to its predecessors, is a) it’s easier to implement, and b) it’s more flexible, because it doesn’t require an entity table for the value it summarizes by. For example, in today’s demo we summarize by account number without needing to have an Accounts table, whereas earlier methods required a Donor table to summarize by donor, or a Products table to summarize by product.
To be clear: with this method it’s fine if the entity table does exist, but it’s not necessary. Either way, the technique will work.
So, the user enters individual receipts into this portal…
…and the summary portal aggregates them by account number.
The Count and Amount fields (s_count and s_amount) are defined as follows:
When we view them through the lens of a relationship (e.g., from the perspective of an Expense record), they will summarize just the related records we happen to be viewing… and they actually make two appearances in this demo:
- In the summary portal (to summarize by account)
- Below the main portal (to summarize all receipts in the portal)
We’ll return to number 2 in a little while, but for now let’s concentrate on number 1.
In this case, we’re going to add a second occurrence of the Receipt table, called Receipt_Summary, and link it this way:
This new table occurrence serves two purposes:
1. The fields within the summary portal are based on Receipt_Summary, although the portal itself is based on Receipt.
2. Receipt_Summary plays a critical role in the portal filtering calculation.
And, finally, here is the portal filtering calculation, along with the sort order.
Since the purpose of the summary portal is to summarize by account, it comes as no surprise that we would want to sort the portal by that field. But what’s going on in the filter calc? The key to understanding it is to a) recall our objective: show only one row per account number, and b) think about the predicates linking Receipt to Receipt_Summary.
The consequence is that, from the perspective of Receipt, only the first matching id (per account and per parent expense) will be visible when we look across to Receipt_Summary.
Well, I think that’s enough about the Summary portal. But there’s still a bit more to say about the total “Count” and “Amount” fields that appear below the main Receipt portal. They are simply a second instance of s_count and s_amount but this time they come directly from the Receipt table occurrence (as opposed to Receipt_Summary).
When I first built this demo, s_amount was not tracking changes in the Receipt portal in real time. All other summary values were updating, but not this one. And it wasn’t just a matter of clicking on the background — to get the value to display, I had to perform an action that would refresh the field contents, such as: clicking in the field, changing to another record and back again, performing a Refresh Window script step, etc.
