Hepl for Formula of Balance Column on AppSheet

[leecoong]

Thanks to the community for help with the formula for  BALANCE column.

[Steve · Accepted solution]

(
  ANY(
    SELECT(
      table[Balance],
      (
        [_ROWNUMBER]
        = MAX(
          SELECT(
            table[_ROWNUMBER],
            ([_ROWNUMBER] < [_THISROW].[_ROWNUMBER])
          )
        )
      )
    )
  )
  + [Receipt]
  - [Payment]
)

Replace table (x2) with the name of the table.

[jyothis_m]

I think you can use a spreadsheet formula for Balance Column

=R[-1]C+RC[-2]-RC[-1]

[Steve · Accepted solution]

(
  ANY(
    SELECT(
      table[Balance],
      (
        [_ROWNUMBER]
        = MAX(
          SELECT(
            table[_ROWNUMBER],
            ([_ROWNUMBER] < [_THISROW].[_ROWNUMBER])
          )
        )
      )
    )
  )
  + [Receipt]
  - [Payment]
)

Replace table (x2) with the name of the table.

[jyothis_m]

Hi @Steve I have a doubt between App formula and Spreadsheet formula entered in Appsheet. Which one is better and faster for sync. 

[Steve]

An expression in the app itself is preferred to a formula in your spreadsheet.

[leecoong]

Excellent! Thank for Hepl.

[Ferret]

Hey Steve. I am using this code in pretty much the same situation and I am getting an app error of the following:

"Unable to fetch app definition. Ran out of resources evaluating: FORMULA"

This is the formula I am using in the BALANCE VIRTUAL COLUMN:

(
  ANY(
    SELECT(
      ProjectedItems[Balance],
      (
        [_ROWNUMBER]
        = MAX(
          SELECT(
            ProjectedItems[_ROWNUMBER],
            ([_ROWNUMBER] < [_THISROW].[_ROWNUMBER])
          )
        )
      )
    )
  )
  + [Amount]
)

In my testing around, I think it had something to do with using the balance field for the calculation of the balance virtual column.

In your opinion, is this a bug or a limitation of how I am trying to ideally achieve this?

[leecoong]

I put the formula on Spreadsheet, it syncs back and forth the Appsheet a bit slow. Moreover, rows containing formulas on GGS are easy to delete by mistake.

[leecoong]

 When entered directly, this formula gives the correct result in the Balance column, but when editing the entered value, it does not automatically update the calculation result.

[Steve]

How/where are you editing the entered value?

[leecoong]

On the Ux View. I correct the data or delete a few rows entered, the final result in Balance column is not corect.

[Steve]

Try using a virtual column rather than a normal column for the balance.

[leecoong]

After editing the value or deleting a row, it must edit all the remaining rows to calculate the correct result. So what is the solution to this problem? What is the formula in the Virtual column Balance? Thanks for your help always.

[jyothis_m · Accepted solution]

@leecoong The problem here is when a single row is edited only that particular Row's balance only gets updated, remaining rows won't update automatically.  In that case you can make a BOT to run the below action whenever any row is edited. You can add a dummy column is spreadsheet just to update it whenever any other row is edited so that balances update in that rows as well. In referenced rows you can enter

SELECT(Table[_RowNumber],[_RowNumber]>[_ThisRow].[_RowNumber]) so that all subsequent rows update when a row in between is edited.

In referenced action you can make an action to update the dummy column e.g. using NOW() expression

I have made a similar app for maintaining balance sheet. In that app whenever any row is edited all subsequent balances update automatically as the formula is in the spreadsheet. I found the spreadsheet formula much easier method in this case as i don't need to update all rows. @Steve 

[Steve]

---

@Ferret wrote:

"Unable to fetch app definition. Ran out of resources evaluating: FORMULA"

---

This suggests to me that you're working with a very large data set. You will probably need to choose a different approach.

[Ferret]

Thanks for your reply. The table currently has 23 records, and the error happens a second or two after saving app definition on PC.

Ill probably have a lot more records, so I will be taking another approach, but this error is new to me and I’d like to know more about it so I can avoid it in the future.