Over the last couple of weeks some customer requests have allowed me to work on two pet projects I’ve been wanting to commit to for a long time.
One is to eliminate the use of summary fields. Val and I have disagreed ‘energetically’ about the use of these for ages, but every time I accidentally go to a layout with one or more of these on it and have to wait for the darn progress bar to complete, or to a report where I’m constantly waiting for screen refreshes, the same conversation starts - me: “we should never use these things”, Val: “they just work”.
Personally, I thing Claris should add a new compatibility feature: “Not Recommended For WAN use” and summary fields would be top of my list for this.
An overview of my solution to this consists of:
A new generic reports table including key fields to link to tables that would normally include one or more summary fields, a quantity of ‘subtotal1’, subtotal2’, etc. fields, and a few global ‘grandtotal1’, ‘grandtotal2’, etc. fields and a batch number field.
A new global batch field added to any table that would normally have a summary field included within it.
A dedicated table occurrence group containing only the tables required for the report and the new generic reports table linked to the table, that should have had the summary fields included, via the keyfields and the batch fields with create records on the side of the reports table.
Within the report creation script after the finds, layout navigation and sorts have completed, there is a generic ‘create summary’ script that is called using JSON parameters with an array for each sub summary part included within the report. Within the JSON the following are passed to the generic script: the sort field (that each subsummary part is based on) leading or trailing, the names of the fields to be totaled, the names of the reports table fields that are to store the results and the name of the batch field.
The generic script first creates a UUID batch number in the global field that the report is based on and loops through each record totaling the specified fields into variables. Loops are exited if a change of value within the specified sort fields is found and the script either populates the specified subtotal field in the ‘reports’ related record of the first or last record within the loop depending on whether leading or trailing has been specified. This also creates the key and batch fields within the reports table (hence when exiting the report layout the related records created can be found and deleted by finding the batch number field).
The script isn’t mature enough as yet to publish an example but the reason for providing this overview is to report on the results.
After testing the values comparing this approach with the same data set using summary fields we carried out some speed tests.
The problem with layouts containing summary fields is that the end of script is reported prior to the date of being rendered, therefore at the end of the report script, before recording the end time we included a go to first record go to last record go to half white point and then back to the first record refreshing the window each time, which would be a common thing to do while checking the results.
We design on the premise that data processing performance is prioritised over report generation, but were still a bit nervous how this approach would compare to using summary fields. To say we were surprised would be an understatement. Using a resulting found count of only about 1200 records and a layout with 2 subsummary parts and a total in only the trailing one, the test took 28 seconds using the summary fields but only 9 seconds using the scripted approach.
The other benefit is that we have completely smooth scrolling in the report, whereas the summary field version is constantly pausing to refresh.
The only negative we can currently see is that this is not dynamic if the data set is adjusted by omitting records. However, I don’t believe I’ve seen a customer do this and we’d customise the menus to prevent it.
A little setup is required, but once done all we have to do is call a script passing parameters as JSON and can delete all our summary fields.
To finish up, I could have run the grand totals alongside the above to set the final global totals fields. However, I’ve a complementary subscript that is called passing the field names as JSON, which builds up another JSON object passed back to the calling script containing the fields names and their totals using this to calculate each total:
While (
//initialVariables
[
~counter = 0 ;
~count = Get ( FoundCount ) ;
~theField = GetField ( $totalField ) ;
~val = ""
] ;
//Condition
GetAsNumber ( ~counter ) < GetAsNumber ( ~count ) ;
//logic
[
~counter = ~counter + 1 ;
~val = ~val + GetNthRecord ( ~theField ; ~counter )
];
//result
~val
)
I like this calculation, as it has always frustrated me that we can’t sum a list like the Excel Sum() function without workarounds. With the introduction of While() we can now do this without looping through records and it is pretty quick.
I hope some of the above is of interest.
Regards
Andy
