Adventures in sorting

A legacy report created in 2004 in the Advanced Accounting 5.1 version by another programmer that was later ported by us into the Advanced Accounting 7i and eventually to the  Adv 8 version, suddenly started to fail today after not having been changed since 2018.  Instead of producing a three page output, it was producing 85 pages of output in a report preview.  The problem was not the data nor had the program been recently changed.  So what happened?

The SORTA command has its limitations (that can be overcome):

This program was using a sort array (SORTA) command to sort items placed into array because an index or key field was not available for the inventory category field and the report needed to sort by that field.

While users can have a virtually unlimited number of inventory items in the Advanced Accounting system, this particular custom program was reading each inventory record into a memory array and then sorting on that memory array to produce the desired results.

The SORTA command has been available since the 16-bit TAS Professional 4.0 version on which Advanced Accounting 4.0 was based (and even older versions, for example TAS Professional 3.0, had a SORT command that worked differently).  In the 32-bit Windows versions of Advanced Accounting starting with version 6 (TAS Professional 6 and TAS Premier 7i and 7ix), the SORTA command behind the scenes uses a  Delphi TLIST object to accomplish the sort.  This command relieves the programmer from having to develop their own algorithm to sort memory values.

The TAS programming language SORTA command used to sort memory arrays has a limitation of 65,535 values.  This original custom program had been designed to read each record, one at a time, into memory and then sort and perform other logic from there.

But the end user now has 65,649 inventory items. That isn't a problem for the system in general to handle; however, it is problem for the way this custom program was sorting the information and it now created havoc based on the program design as the "read"array values were now being misinterpreted.

The solution was to condense the number of arrays to just a mere 450 internal arrays (instead of over 65,000) as that was how many different category values existed in the end user's inventory file.  The revised program creates those dynamically when each inventory record is read by using a second corresponding array (rather than combining them later after being read, which is how the program was originally designed).

Other solutions would of course be possible including defining the inventory category field as as index (but this would require changes to a number of other programs and would involve restructuring the data, and would also make the system harder to upgrade in the future) or by creating a new data structure with the appropriate keys that each record is saved to and updated in a manner similar to the revised array approach (but this creates multi-user issues that have to be addressed, and the program is going to run much slower as this is fairly hard drive intensive alternative) and then changing the program to report on that new temporary file, or a third solution would be to access the data via ODBC and query the data via SQL (but that would require using an outside process or a separate Delphi or other program or our SQLQUERY.EXE or the Pervasive/Zen equivalent, assuming the user is utilizing the newer database engine).