Introduction
Recently a client asked me to implement a virtual list reporting framework similar to the one I wrote about a few years ago in Virtual List Simplified. I added the framework to the client’s hosted file, and things went smoothly until I ran a report similar to the one shown below… and observed that the repeating summary field at the bottom wasn’t rendering. The same report worked flawlessly a) offline, and b) when hosted on FMS 19.4 and earlier, but when hosted via FMS 19.5 or 19.6, the summary repeater was malfunctioning.
To cut to the chase, the culprit appears to be a bug with the Calculating Summary Fields on the Host feature introduced in FMS 19.5. If you visit the aforementioned page (as of 12/28/2022 at any rate) you will learn that…
Summary fields are now processed on the host instead of the client if the custom app is hosted by FileMaker Server 19.5 or later, and these conditions are met:
- The file is hosted and open.
- The summary fields do not contain fields from External SQL Source (ESS) tables.
- The summary field is not an unstored calculation requiring client computation (such as using plug-in functions).
- The summary field is not a global field.
- Sort is not in progress.
- The found set to summarize is larger than 25 records.
- The host CPU usage is not higher than 25% when summarization begins.
Correcting for the imprecise wording of items 3 and 4 (rather than “is not”, they should say “does not reference”), it appears that there may be an issue with item 3 attempting to evaluate server-side when the server does not have enough information to perform the operation correctly. To see this in action, first host today’s demo file on FMS 19.5 or 19.6, and open the file in FMP 19.5 or 19.6.
Demo file: Summary-Field-Host-Calculation-Bug.zip
Account: Admin
Password: Admin
Standard Report
To see the bug in action (and assuming you followed my instructions and hosted the file on FMS 19.5 or FMS 19.6), click “Standard Report” and you will see a report similar to the one shown in the first screen shot above, and with empty totals at the bottom.
Here is the standard report in layout mode, with five instances of a calculated repeating field in the body, and four instances of a corresponding summary field for repetitions 2 through 5.
Here is the definition of cell_num_r. Note the 150 repetitions, that the calculation is unstored, and that it references a $variable.
Here is the definition of the corresponding summary field…
…and, as a reminder, when we run the “standard” report, we get empty totals rather than the summary values we would expect to see.
Report with Workaround
Now click “Report with Workaround” and note that this time you are seeing proper values in the totals row.
Here is the workaround report in layout mode, with a different summary field in the totals row, defined like so:
And here is the definition of cell_workaround_r:
Bottom line: The trick is to do something to force FileMaker to evaluate the summary field client side rather than server side, and no doubt there are countless ways to accomplish this. Here I’m sharing the first thing that worked for me as I was flailing around looking for a way to get my summaries to work properly.
Closing Thoughts
1. In today’s demo the calculated fields reference a $variable (a.k.a. a “script” variable). I’ve seen the same problem with $$variables (a.k.a. “global” variables) under similar circumstances. I have not nailed down all the nuances of this particular bug, but wanted to share what I currently know sooner rather than later.
2. If you haven’t already seen it, don’t miss this great writeup (Claris Releases FileMaker Server 19.5) by Angelo Luchi, which includes some additional ways to bypass host calculation of summary fields.