Today I’m going to address a shortcoming in the “FM 13 Column Chart on Found Set” demo in my previous article. Specifically, I took a lazy path of least resistance re: calculating the chart title.
The challenge was to count the number of states in the found set, and since there weren’t many records in the original demo I decided to use a recursive custom function to build a list of unique states, and then the ValueCount function to count the number of entries in that list.
And that was fine. When the found set was small.
The original demo had less than 500 records, so there was no danger of hitting the 10K iteration limit, but not only does that approach not scale well, but by the time you have, say, 8000 records in the found set, performance becomes a significant issue. And I’m talking about local performance. If you’re on a LAN, or WAN, even a couple thousand records can bog things down.
Well, today we have two demos…
…the first demonstrating the problem with the custom function approach, and the second showing a much faster solution based on the new-in-13 summary list field type.
DEMO #1
If you click the various buttons in demo 1, you’ll note the inverse relationship between performance and found set record count (you’ll also observe that the CF recursion limit kicks in at 9999 records).
And the problem is not restricted to browse mode. If you enter preview mode with an 8000 record found set, you will wait at least 5 seconds before the layout draws (whereas with only 100 records in the found set the layout renders instantly) because the chart object is struggling to calculate the title. You can prove that the custom function in the title is the culprit by going into Chart Setup and disabling it.
And indeed that “solves” the problem, but at the unacceptable cost of losing the count of unique states. Which brings us to…
DEMO #2
The first thing you’ll note about this demo is that it has ten times more records than demo 1.
The second thing you’ll note is that performance (assuming you’re testing this locally, not on a LAN or WAN) is quite zippy — on my machine it takes about 1 second to process 50,000 records, and less than 2 seconds for 100,000 records.
Apart from the total number of records, the only difference between this demo and its discredited predecessor is the method used to count the unique states in the current found set. This time we’re going to use a value list to build a list of unique states for the current found set… here’s how.
Step 1: define the two highlighted fields
Step 2: Relate Customers to a second occurrence of the same table like so
Step 3: Define this value list
Step 4: Define the chart title like so
And that’s all there is to it.