Column value depending on previous row data (via. Actions/Workflow)

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).

I believe you are running into a state issue. When changes are made, AppSheet attempts to retain the state of the data based on the time the change was requested. So, the portion of your formula `[PREVIOUS ROW].[BALANCE]’ is retaining the value for that second row based on when the changes were requested and is NOT updated with changes made as a result of the Workflow/Action processing.

In other words, you are correct that it is retaining the original value and not the updated value.

You can avoid this by using a LOOKUP() function instead. I believe, if you change your Balance calculation to the expression below, you’ll be good.

BALANCE = LOOKUP([PREVIOUS ROW], "Ledger", "PREVIOUS ROW", "BALANCE") 
          - [TRANSFER OUT] + [TRANSFER IN]

@WillowMobileSystems

Thanks for taking the time and confirming the suspicion. However, I tried using your recommended solution (with slight modifications) and it suffers from the same problem. LOOKUP also sees the older values.

LOOKUP(
    [PREVIOUS ROW], 
    "Ledger", 
    "ENTRY ID", 
    "BALANCE") 
- [TRANSFER OUT] 
+ [TRANSFER IN]

Tried using SELECT, instead and this too returns older values:

ANY(
    SELECT(
        Ledger[BALANCE], 
        ([_THISROW].[PREVIOUS ROW] = [ENTRY ID])
    )
) 
- [TRANSFER OUT] 
+ [TRANSFER IN]

I see references to Before and After Values in a couple of places, but I’m not sure if I can use, _THISROW_BEFORE and _THISROW_AFTER, in my context.

[EDIT 1]
The Test button in the Expression Assistant, shows expected values.

Ok. I admit I didn’t test it before hand and was operating off of memory from over a year ago to a similar feature I was trying to create. In the end, it was completely changed anyway so i don’t have reference to where I ended up with it.

I do know that if you tracked the running balance in a separate table, you will not have this issue.

Lastly, you have confirmed that the last row IS be processed through your Action to be updated? You can easily test it by temporarily setting the balance to some obscure value rather than calculating it.

When I have a moment, I may try to re-create your issue.

Laughing real hard on this one, because I’m currently breaking my head keeping the running balance, or rather the [END OF DAY BALANCE] in a separate table. It works, but complicates the application by several orders of magnitude.

Once again, thanks for taking the time.

I’m sorry, I do not understand what you meant by this.

Typos. I meant to ask the question “have you confirmed that the last row IS being processed…”

I understand what you mean. Its a multi-user distributed system and does take a little adjusting to get use to the way of managing changes. Still some seem overly cumbersome.

Additionally, AppSheet is still young and feeling its way to maturity. We developers need to help by submitting suggestions and recommended changes. The issue you are experiencing may just be one of those to submit…if it hasn’t already.

1 Like

Yes, I have. Every row is being processed.

Agreed. I’m loving AppSheet, actually, it’s kinda like those 2-minute ruby CRUD apps, only better,