I had a fun challenge today. A customer had a table with an odd structure. One column was a payment amount. It was followed by 40 different columns for various fees. He wanted the payment amount to be applied to the fees from smallest amount to largest amount until the payment was used up. The report was to show the balance for each fee after the payment was applied.
Normally this type of data would be vertical. There would be a separate row for each fee and only 2 columns (fee name and the fee amount). With that structure you could sort by fee amount and use a variables to apply the payment to the records in ascending order. But with 40 fixed columns I had to load the values into an array, and then put the values in the right order.
I loaded the fees into the array as strings, combining the amounts and fee names into one element looked this:
24.50=xyzFee
This allowed the fee name to follow the amount through the sorting process.
Once I loaded all these strings into the array I used my bubble sort formula to re-sort the array from lowest amount to highest amount. In the bubble sort comparison I used the Val() function to convert the strings to numbers. This put them in order by their true numeric value.
Then I wrote a second loop to apply the payment. It steps through the array, which is now in the right order, and applies the payment to the fees, one at a time. Each fee amount is reduced to zero while the payment value is reduced by the amount of each fee. If the payment’s remaining value drops below the amount of the next fee the remainder is subtracted from that fee and the payment is reduced to zero. Any fee amounts in the array beyond that point stay the same.
Last, to display the results, I wrote 40 separate formulas, one for each fee. Each of these formulas loops through the array looking for its specific fee description. When it finds the element with a matching description it uses the Val() function to convert that string to a numeric value and displays that value. This is the relatively simple loop formula:
EvaluateAfter({@Build Fees Array});
stringVar Array Fees;
Local numberVar i;
Local numberVar fee;
FOR i:=1 to ubound(Fees)
DO (
if 'xyzFee' in Fees[i]
then Fee := val(Fees[i])
);
Fee
Not many people would think this process was fun, but I did enjoy mapping out a creative approach to a unique requirement. And it was less than 2 hours from the requirements to a validated report. The next time your report requirements are a bit unorthodox, keep me in mind.
(For examples of my most popular formulas, please visit the FORMULAS page on my website.)