I’ve been reading Sean Corfield’s examples on how to use the Closure library. This still seems a little mystical to me since I haven’t made use of closures in programming before.
To ease my befuddlement, I wrote a test for the Closures library. The use case for the below example is summing a column that contains non-numeric values. You can recreate the test by following these steps:
Create a table containing the three columns.
- InvoiceID = Primary Key, autonumbered
- CreateDate = Date
- Amount = varchar 50
Now run the following code to populate the table:
INSERT INTO Invoice
( CreateDate, Amount )
VALUES
(
)
You now have 100 rows of data and every 5th row contains the string ‘Not Applicable’.
The goal of this is to sum the column ‘Amount’ and output the result. The closure has an internal scope and can keep the running total. The function body used to create the closure is rather simplistic and takes two arguments, Mode and valueToTotal. The logic of the function simply adds the valueToTotal to the running total. Passing a mode of ‘Display’ will output the current total.
Now, we write all the records from the Invoice table to the screen. Note the use of the closure to output the amount. We also call the same closure method with a different mode to output the Grand Total.
SELECT InvoiceID, CreateDate, Amount
FROM Invoice
#DateFormat(CreateDate, "mm/dd/yyyy")# #total.call(mode='sum',valueToTotal=Amount)#
Grand Total: #total.call(mode='Display')#
Below is the result
12/09/06 8151
12/10/06 4885
12/11/06 9525
12/12/06 1001
12/13/06 Not Applicable
........ snip out 95 more rows ....
Grand Total: 384272
The grand total was calculated using the formula in the closure. It would be trivial to bind additional values to the function if we wished to complicate the formula, perhaps to add a markup value or a discount value.
This code works, you can see that. I still have plenty to learn about closures so please comment if you have thoughts or criticisms.
Update:
Sean recommends another method to create the closure which is much more succinct, using more features of the ClosureLibrary. To use:
Remove the runningTotal function definition. Then replace the <cfset total = cf.new(runningTotal) > with the following snippet:
Finally, since both arguments are now required and the Closure library uses the argumentcollection, add an argument for valueToTest to the grandtotal section as follows:
Old
Grand Total: #total.call(mode='Display')#
New
Grand Total: #total.call(mode='Display', valueToTotal=0)#
When you run the code, the total is calculated as before. Note, The arguments are defined in the cf.new function along with binding the initial value of ‘total’.
Thanks Sean!