In Part 1, I explained how I started exploring different options to meet a customer request for a specific report (not a standard subsummary report) with acceptable performance (not a resource hog or more than a minute to display).
Combine the Knowns to Solve the Unknown
By thinking through all of the tools and methods I had tried or read about, I was able to assemble a list of the steps I needed to take, and what tool or technique to use on each step:
- Gather all the parent and child IDs – GetNthRecordSet( ) function
- Convert the sort-able data that references a giant value list to a number that can be sorted easily – Position( ) function
- Set the IDs and sort numbers directly in the report records – via portal, to avoid the commit problem (transactional commit)
- Report layout – Display related data in dummy records to cut down on CPU usage (similar to virtual list technique)
- Sorting – Sort on numbers that have been set into fields by a script, rather than sorting a related field based on a related value list
Here’s how that gathering of tools played out in real life:
- Go to the list view of the parent records.
- Enter a loop with a variable ($Counter) set as a counter. $Counter will be used later to sort the records into the order they were encountered.
- Loop through the parent records using the GetNthRecordSet( ) function, using the counter variable to determine which record to look at.
- On each parent record, append a global variable ($$Keys) with the parent record’s ID, two pipe characters (placeholders), and $Counter.
- Begin a second loop with its own counter. Using the parent/child key, loop through all the related child records and append $$Keys with: the child record’s ID, the sort order required by a large, user-generated value list, and the parent record’s $Counter.
The calculation for the sort order based on the giant value list is simply:
Position ( ValueListItems ( "GiantValueList" ; "File.fp7" ) & "¶" ; ItemFromChildRecord & "¶"; [starting at] 1; [occurrence] 1 )
This converted the item name to a number, based on where it was found in the value list. So instead of sorting on a related field based on a related value list, I would simply sort on a number field set by a script.
By this point in the script, I will have collected all the values I need. The data looks like this:
1096718||1 471067|362|1 471068|131|1 471069|438|1 471070|48|1 471071|779|1 471072|337|1 8721684|738|1 1096732||2 419861|131|2 2458113|48|2 2458114|3998|2 2459828|779|2 8721807|738|2 1096733||3 575704|362|3 575705|438|3 575706|48|3
Decoded, it means:
Parent Record RecordSerial | | SortOrder 1096718 | | 1
Child Record RecordSerial | PositionInValueList | SortOrder 471067 | 362 | 1
All that’s left to do is to loop through the report records and set the fields. Once the key fields are set for either parent or child records, a relationship is established, and I can display whatever I want from that record. In this way, I’m not moving all the data, just the key fields allowing me to place related fields on the report layout.
I’m also setting the sort order in a number field, which will let me group parents and children together. The middle value of the child record data represents the sub-sort order below the parent.
Setting Fields Quickly
When I was setting fields before, I had problems with committing records taking up time. Once again, Todd Geist (and this time, also Jason Young) to the rescue. Looping through a portal does not commit the records as you move from record to record. So I created a third relationship, a one-to-many self-join, using a global key field:
Reports::zzgk_NumberOfRows = Reports::zzk_Row
where Reports::zzk_Row is a pre-populated field.
For the relationship, it’s a text field that was set to the record number of the record in question at time of creation. For sorting, there’s a number version of the field. (And in this scenario, I went ahead and created 30,000 records in advance. That’s probably enough for almost all scenarios for this particular report.)
So now, to continue with the script concept:
- Go to a layout based on the Report table that has a transparent scrolling portal based on the Reports::zzgk_NumberOfRows = Reports::zzk_Row relationship. There are no fields in the portal, for speed’s sake.
- Set field Reports::zzgk_NumberOfRows to (PatternCount ( $$Keys ; “¶” ) + 1 ). This tells the portal how many rows of report records to show.
- Loop through the portal, setting either the parent key and clearing the child key (from the last time the report was run) or vice versa: set the child key, and clear the parent.
- While in that portal row, also set any and all sort values.
- Exit the portal, commit the record, and all the changes are committed at once.
- Navigate to the actual Report layout, and sort the records based on the Parent’s sort number – which places all the parent and children records together in the order that the user had originally sorted the parents – and then sub-sort by the value list sort number for the children records. Because these numbers are set by the script rather than calculated on the spot, they sort very quickly.
- Enter preview and present the print dialog.
The fields on the Report layout are actually related fields from the parent and child. Each report record has both sets of fields in the body. If it’s a parent record, the child fields are empty, and set to slide and shrink the enclosing part (the body). If it’s a child record, the parent fields collapse out of the way, and the body once again shrinks. In this way, I just tell a record what it is and use sliding to make it look right.
To make it clear which is which, I put conditional formatting on the parent record:
[ Not IsEmpty ( Self ) ; Turn the parent fields gray ]
This twelve-step process did the trick. And it was much faster than anything I had built before it. Better still, it worked!…Well, almost.
I started having trouble with “ghosting” — that is, when a relationship continues to show the last value it showed, even though the relationship key has changed. As best as I could tell, this was simply some freaky by-product of walking through the Report records through a portal. Like I said before, when you’re innovating, you never know what FileMaker is going to bless or curse.
My solution here was to mirror other processes in this solution and open a small window in which to do the search and set with the fields. Then, when the report was set, I would close the temporary window, and navigate to the report once I was back in the original window. This worked like a charm.
Time for Some Speed Tests
Here are some results based on small-found-set tests that I ran:
- 272 parent records / ~ 4500 children records – Elapsed Time: 2:45
- 213 parent Records / ~ 2770 children – Elapsed Time: 3:35
- 126 parent records / ~ 830 children – Elapsed time: 0:42
- 208 parent records / ~ 1540 children – Elapsed time: 1:10
The apparent lack of consistency is from variations in the amount or type of data being referenced.
But the times are well under the five to ten minutes my very first scripts were showing. And the CPU dropped from 100+ percent solid for five minutes (sometimes peaking at 190%) to 15% peaking at 30% here and there, running under three minutes most of the time.
That, for me, was the process of brainstorming my way through this problem. I expect there to be some forehead-slapping suggestions like, “Why didn’t you just use the MagicDoWhatINeed( ) custom function?” And my answer will be the same to all suggestions: “Next time, I probably will!”
The point is to try to demystify the innovation process in a continuing effort to become better developers. Also, to encourage everyone to continue chasing down those difficult problems and making them submit.
The bad news: it’s just a lot of hard work sometimes. The good news: if it’s hard, then many will avoid it. That means the playing field is open to whoever wants to be the next famous FileMaker innovator, and acquire the related spoils.