Calculate Account Balance (i.e This row vs previou...

Skip2MiLu · 12-15-2021 02:28 PM

Hi Guys

I looking for a virtual column called [Balance].

Hopefully the below picture and remarks explains my situation. I would appreciate the support.

I think the expression should be something looking for the following arguments

IF([Category] = "Opening Balance}, True = This Row [Balance Value], False = This Row [Amount] + [Balance] of the previous dated row of the same account name as this row.

Marc_Dillon

Firstly, you should review how the inventory sample apps are set up.

https://www.appsheet.com/SampleApps

To answer your question:

Assuming you have an Account Table, that:

includes at least 2 records, a “Cheque” and a “Cash” record
is a Parent table to the displayed Table in your screenshot

…and assuming there is only ever one “(Opening Balance)” record (per account),

…you could use this, or something like it, in a Virtual Column on the Account Table:

ANY( SELECT(
  [Related...][Balance] ,
  AND(
    [Account] = [_THISROW] ,
    [Type] = "(Opening Balance)"
  )
) )
-
SUM( SELECT(
  [Related...][Balance] ,
  AND(
    [Account] = [_THISROW] ,
    [Type] <> "(Opening Balance)"
  )
) )

Calculate Account Balance (i.e This row vs previous dated row with conditions)