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.
The underlying architecture of the demo is straight-forward. The parent table (Expense) is joined to the child table (Receipt) via a standard primary-to-foreign-key equijoin relationship. On the Receipt side, the relationship is set to allow both creation and deletion.
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.
It would be great if all that was necessary to pull this off was a filtered portal with a clever calculation to isolate unique entries… but I’ve never found a method that didn’t require some sort of additional schema-level “helper”, and today’s method is no exception.
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.
We joined them via two fields: id_expense (so we would only see entries for the current parent Expense record) and account.
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.
And we can confirm this by examining a found set of receipt records corresponding to Receipt portal in the first screen shot above. The highlighted rows are the only ones that pass the filter test.
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.
Clearly this behavior was not acceptable. I was inclined to use a Refresh Window script trigger, but decided to try a different approach first. So, I overlaid an invisible Receipt portal, and voila, no more pesky problem.
Very nice, Kevin! Very clever use of figuring out the first record from each account. Well done.
Thanks Darren.
Cool, Kevin!
Thanks Stefan. When two of the smartest guys in the FileMaker universe take the time to comment approvingly, you know you must be doing something right :-)
And now response from other end of spectrum ;-) From newbie view, it was clear, easy to follow, and you nailed the logic for me also; I created test file from scratch using different examples.
The summary portal did not refresh and I came back to find out how. Using invisible portal trick is cheesecake! I read web view can force refreshes also but have not tried it. Invisible portal is much better. It does not flash like Refresh window[flush cash]. Thank you so much for both these incredible tools!
I’m trying to design a packing list summary based on a table of individual items that are scanned in via barode. I think this is exactly what I have been looking for, I’ll try it out later tonight and see if it works for me.
Hi Lincoln,
I never say “never”, but printing a portal isn’t the standard way to solve this particular problem. Under normal circumstances, you would want the packing slip to be generated from a layout attached to line items, as opposed to the parent. A simple summary report based on line items should do the trick.
Regards,
Kevin
I like your technique of using the two summary fields on the Expense layout from the Receipts table, instead of two SUM() fields created in the Expense table. I have never thought of trying that – nice. I am wondering however, why they were placed in a portal. I went into the demo file and removed the portal and they still worked. What is the portal for?
As per the final two paragraphs it was necessary (in FM 11 for Windows at any rate) to use the invisible portal to get s_amount to update in real time. I could have used a script trigger or some other method instead, but I chose to use an invisible, one-row-high version of the Receipt portal instead.
You are a genius :)
This portal filter technique is really nice ! Thanks for this insight !
Andries
Coming from one of the smartest guys I’ve ever met, that is high praise indeed. Thanks Andries.
Best wishes,
Kevin
Thanks, this is excellent solution with really nice explanation.
I wast trying to do a similar example (on FM10, so multiple rows in “Summary” portal – no problem, I can understand it).
Was curious about forcing the “s_amount” summary field to refresh.
It seems that it is not necessary for it to be laid inside the “triggering” portal. In fact, it works when there is anywhere on the layout any Receipt portal, that IS NOT SORTED. It could have do the main Receipt portal, that shows the Receipt’s records, if not set to “Sort portal records”.
That’s just an observation, nothing more…
Milan
I just did some experiments in FM 11 on both Mac and Windows with the hidden portal removed. At first it seems like everything’s fine, but before long, the value (s_amount) is either not updating in a timely manner, or disappears completely.
You are right, I should have more extensively tested this. Typically, deleting of the first row does not refresh the summary.
But when the main portal is set not to sort, it still seems to work…
Still no doubt that original solution with underlaying invisible portal is the best one, I’m just wondering why setting of portal to sort records prevents summary from refreshing.
Milan
Hi Kevin
thanks for excellent postings which explain things so clearly.
I have a query and would like to explain by elaborating on your summary within a portal solution.
In order to mimic my database, I have built on your solution, adding a join table and submitter table. All the relationships go through the one join table. Your solution still works fine of course.
I would like to know how to produce a summary report based on time periods, (in my database specifically Spring, Summer and Autumn terms for any given year) which include data from all expense records. In your example, this will summarise data from both Suzanna and Billl and any other submitters active in this period.
I need to have a count and total of accounts, a list of any submitters who submitted expenses in this period and a total number of submitters.
This report therefore needs to summarise across a number of expense submissions, and extract data from multiple relationships. I imagine this requires portals similar to your solution but built on sub summary parts of a report rather than a footer, but I don’t know how to get this to work. When I try this, the portals only display the first matching record and do not pull from multiple expense records.
I hope you will have a neat suggestion for solving this?
Many thanks
Ben Parkin
See if this helps. Maybe a combination of Kevin’s article and this?
http://filemakerhacks.com/?p=5950
That’s a great solution, saved me a lot of time – many thanks indeed!
Mr Frank,
Thank you very much! Just what my enduser asked me to build. I’ll tell her I got it from you!.
How do I create this with unique values? Rather than a total of number fields, a total of specific unique values?
Kevin, your technique was of great help. Thank you for sharing. How can I print the summary? I am using your technique in invoice solution and having hard time getting to print the summary with the rest of the invoice details.
Portal printing is notoriously tricky. Check out my articles on virtual list reporting for some alternative ideas.
Wow… This was very helpful.
Thank you.
Hello,
Thanks for your Summary Report in a Filtered Portal tutorial. Doing my best to follow, I created my summary portal and it totals the “amount” field correctly but it lists your “account number” field multiple times, one time for each original record entry in the parent portal. The summary amount total is correct, just repeated on each portal row as would be expected. I know this is an old thread but any suggestion as to what I might have done wrong? Thanks