A customer recently asked for help with a formula. She was creating an index at the end of a report to show page numbers for each item within the report. Her formula used nested FOR loops to put the elements of the array in alphabetical order. It was similar to this handy formula that I was given once to do a “bubble sort”:
stringvar array MyValues;
FOR i:=1 to ubound(MyValues)-1 DO (
FOR j:=1 to ubound(MyValues)-i DO (
if MyValues[j] > MyValues[j+1] then (
temp := MyValues[j];
MyValues[j] := MyValues[j+1];
MyValues[j+1] := temp;
The problem we had was that the formula would work fine for small arrays, but would generate an error on a larger array. The error would say:
“A loop was evaluated more than the maximum number of times allowed”
The maximum number of loops in a single formula evaluation is 100K, so usually this error means that you wrote an infinite loop. But in this case it was legitimate. Because an array can be up to 1000 elements, and we are doing one loop within another, this formula could theoretically generate up to one million loops. A correctly done bubble sort for 1000 elements should max out at about 500K, well over the limit.
To stay under the limit on longer reports, we separated the two loops. With the formula above we wait until the end of the report and do the sorting all at once. Instead I added a sorting loop to the formula that adds each element to the array. This way we sort the array as the elements are added. And because this formula evaluates each element separately, the maximum number of loops at one evaluation would be just the size of the array. Problem solved.
But in doing my research on this I learned something new. I learned that 100K loop limit isn’t a hard limit. You can override this limit to make it both smaller and larger as needed. All you have to do is start the formula with a line like this:
option loop 200000;
This would double the limit from the default of 100k to 200K, making more nested loops possible. Crystal let me set the number at one trillion without complaining, although I didn’t actually try to create a loop that ran that many times. I still prefer to avoid nesting loops when possible, but this might solve a problem some day.(For examples of my most popular formulas, please visit the FORMULAS page on my website.)