So, I am just trying to implement a simple cash ledger functionality as part of my AppSheet learning. Following is my underlying table,
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
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
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,
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).