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.
