LinkedIn
Twitter
So, I am just trying to implement a simple cash ledger functionality as part of my AppSheet learning. Following is my underlying table, Ledger.
I have a column, Balance that maintains the outstanding balance of each row. Each row is a transaction, Entry. That is, either a Transfer In or Transfer Out. The entries are always done serially, oldest on top and newest at the bottom.
The calculation is rather simple:
[BALANCE] = [PREVIOUS ROW].[BALANCE] - [TRANSFER OUT] + [TRANSFER IN]
When an entry is made at the bottom of the list, the BALANCE of just that row needs to be calculated. However, when an entry, anywhere else, is edited/deleted, all succeeding entries need to have their BALANCE recomputed.
Towards achieving this, I first put a virtual column, PREVIOUS ROW in Ledger table and set it to the following:
MAXROW(
"Ledger",
"_ROWNUMBER",
AND(
([ENTRY DATE] <= [_THISROW].[ENTRY DATE]),
([_ROWNUMBER] < [_THISROW].[_ROWNUMBER]),
(ISNOTBLANK([BALANCE]))
)
)
This expression returns a reference to the previous row and it works just fine. Then, I set up an action, "Get Entries to Update" like so:
Do this: Data: Execute an action on a set of rows
Referenced Rows:
ORDERBY(
FILTER(
"Ledger",
AND(
([_THISROW].[ENTRY DATE] <= [ENTRY DATE]),
([_THISROW].[_ROWNUMBER] <= [_ROWNUMBER])
)
),
[_ROWNUMBER],
FALSE
)
The above expression will get all rows that are below the currently edited row. I can verify everything is working, as expected, up to here.
For each of these affected rows, an action is then executed which updates the BALANCE column in those rows:
Do this: Data: Set the values of some columns in this row
Set these columns:
BALANCE = [PREVIOUS ROW].[BALANCE] - [TRANSFER OUT] + [TRANSFER IN]
Finally, the action, "Get Entries to Update" is triggered by a workflow rule of the Ledger table, ALL_CHANGES.
This is where things appear to break down. While the balance of the edited column gets updated correctly, it appears the succeeding columns are seeing โolderโ values in the previous rowโs, BALANCE.
That is if I were to edit the TRANSFER OUT in the second last row and make it 238 (from 138), the value of BALANCE in the same row would update correctly to 380. However, the value of BALANCE in the row below would remain 380. As if it were still seeing 480 in the previous rowโs balance column.
Any help is appreciated and thanks for reading (this rather lengthy topic).
