Last time we looked at several summary reporting tricks, including a conditional subsummary (when an item’s Status is “Scheduled” it will have a value in the Substatus field — otherwise Substatus will be blank). The challenge was to generate a summary report showing Substatus only where appropriate, without seeing any annoying empty gray rows beneath Pending, Cancelled or Completed. And last week’s report worked fine… in preview mode.
Joel Shapiro posted a comment wondering whether I’d had any luck getting the trick to work in browse mode, and suggesting that the virtual list technique might facilitate this. It was a good suggestion, and today we have three demos exploring this approach. Once again the demos are in .fp7 format, but of course you can convert them to .fmp12 format if you wish.
All of today’s demos loop through the found set using the fast summary technique to build up an $$array of values, which are then parsed by the virtual list table. Demos 1a and 1b take a minimalist approach; demo 2 is more convoluted but the end result (in browse mode) comes very close to last week’s preview mode report. [Note: You can learn more about fast summaries here, and virtual list here.]
Demo 1a: conditional subsummary in browse mode, v1a
The basic idea in this demo is to build an array, like so:
Depending on which button you click, the array will either look like this…
…or this:
Note that ValueCount($$array) will determine how many virtual list rows to display when it comes time to show the report.
Also I should mention that to get the dollar amounts to display nicely, I used a custom function. Otherwise the commas and the “.00” would not be there.
The virtual_list table is defined so that each record will correspond to one row in the array. The serial_number is 1 for the first record, 2 for the second record, 3 for the third record, etc., and we want it to be a stored static value, as opposed to Get(RecordNumber).
Here’s a basic table view.
The tab stops aren’t necessary here, since this is just a utility view, but they do convey what’s going on in the array.
Status entries contain a single tab, substatus entries contain two tabs, and salesperson entries contain three tabs, and the tab pattern count provides the “recipe” for conditional formatting on the report layout (we don’t need to apply conditional formatting to the Salesperson row, so there are just two tests… one for Status and one for Substatus).
I should point out that “Tab” is a custom function that, you guessed it, produces a tab character. I could have used Char(9) instead, but the custom function makes the code easier to understand.
Here’s what we see when we click the Status/Substatus button:
And here’s the Status/Substatus/Salesperson version:
In the former case, the value count of the $$array is 7, whereas in the latter case it is 26. In both cases the reporting script locates records where the serial_number is <= the value count.
Not too shabby for a single field on a body part, is it?
Demo 1b: conditional subsummary in browse mode, v1b
In demo 1a, two different arrays are generated, depending on which button is clicked. There’s nothing wrong with that — in fact it’s quite efficient — but it’s not strictly necessary. One of the reasons serial_number is a static number field is that we might want to show just part of the array on our report. For example instead of this…
…we could omit some records and show this:
The only difference between this demo and demo 1a are the highlighted sections of the report generation script.
The notion that from a single $$array you can generate multiple reports, simply by manipulating the found set in the virtual list table is intriguing and potentially quite powerful. (Thank you Bruce Robertson for explaining this to me.)
Demo 2: conditional subsummary in browse mode, v2
I built this demo first, but decided to show it last because a) I don’t actually care for this approach… it strikes me as too much pain and not enough gain, and b) it’s more complicated than demos 1a & 1b. So why am I bothering to show it at all? For one thing, it explores some other virtual list possibilities; also it comes closest to the original preview mode version from last week.
Note that the Status rows are taller than the other rows, and also the presence of the empty separator rows. Here it is in layout mode. Not only do we have a genuine sub-summary part, but there are five fields on this layout, as opposed to one field in the previous demos.
Here’s the array.
The calculated fields use a “GetArrayItem” custom function to parse individual values from the array.
Conditional formatting turns the row gray for substatus entries:
Finally, let’s take a look at the array once more, this time at the Status/Substatus version, and notice that the highlighted rows here…
…positionally correspond to the empty rows of the report.
The status subsummaries need to pull their values from somewhere… and that somewhere is the (apparently) empty rows in the virtual list table. The sub_total field is not visible, but it only contains a value when a row is empty… which is then summarized in the summary_total field in the status row.
As I say, I’m not a big fan of this approach. It’s a lot of work with a small payoff, but it does demonstrate that it’s possible to leverage virtual list with standard summary reporting techniques and come up with a hybrid approach — one that could bear more substantial fruit under different circumstances.