I got a request the other day to show a running balance in reverse order in a portal… i.e., from oldest at the bottom to newest at the top. Surprisingly, FileMaker does not provide an obvious way to accomplish this. [Demo file: running total in reverse]
Running balances, a.k.a. “running totals” are summary fields, defined like so…
…and typically, we expect to see them in ascending date order (as in a checkbook ledger):
The above makes perfect sense since we’re sorted in ascending date order. Until the other day, I hadn’t given it much thought, but it turns out it doesn’t matter how you sort the records, the running total will always evaluate from top to bottom. Here the records are sorted in descending date order…
…and I would argue that in this instance the running balance doesn’t provide much in the way of useful information (except we may appreciate knowing the debits and credits precisely balance out, as indicated by the “0.00” in the final entry).
Returning to the portal challenge, my initial stab at solving it was to simply sort the portal by descending date and display the running_balance field, like so:
Boy was I naive.
I tried a few other things that didn’t work, and one thing that did work, but was embarrassingly over-engineered. To cut to the chase, I ended up throwing an additional occurrence of the ledger table onto the graph, sorting the first relation in descending order, and the second one in ascending order:
The id field (the primary key of the ledger table) was added to ensure that, for a given employee, multiple ledger entries with duplicate dates would behave in a logical manner.
Next I defined a calculated field, reverse_running_balance, in the ledger table…
…which says, in a nutshell,
- if I’m the last matching record, give me the running balance from the first record
- if I’m next-to-last, give me the running balance from the second record
- etc.
Here’s the portal in layout mode:
And here it is in browse mode, this time for a different employee.
And finally, as a complement to the chart types we looked at last week, the data in the ledger table is crying out to be exhibited on a “Positive Negative Column” chart…
…which is simplicity itself to configure thanks to FileMaker 12’s super-friendly chart setup interface.
Works well. Thanks :)
I found a case where I needed to filter visible data, in this case fiscal year, and came up with this resolution:
GetValue (
List ( ledger_to_ledger_by_emp_id::running_balance );
Count ( ledger_to_ledger_by_emp_id::id ) – ( Get ( RecordNumber ) – 1 )
)
While the portal into ledger uses fiscal_year (global field) to filter visible data the relationship between ledger and ledger_to_ledger_by_emp_id also makes use of fiscal_year as a key field. Might also need a script trigger with a Refresh script step on the global field.
Might even be more economical to count list members.
I wish I could take credit for this, but I found a simple solution for a reverse running total on the FileMaker forums from Phil of Caulkins Consulting. Starting with the Amount field:
– Define TotalAmount, the Total (NOT running) summary of Amount.
– Define RunningTotalAmount, the Running Total summary of Amount.
– Define a calculation, ReverseRunningTotalAmount, as
TotalAmount – RunningTotalAmount + Amount
That calc should work as expected for a bottom to top running total!
Without the need for additional table occurrences, it’s pretty convenient. Making math do the work for us!
I thought it was pretty cool, so I thought I’d share as a small thanks to Kevin for all the insight generously shared here on FMHacks.
Great trick
Thanks