Hi, I’m having the debit and credit transacti...

(Ciecy Tan) #1

Hi, I’m having the debit and credit transaction for a customer, may I know how to compute the running total value after each transaction?

(Mary Jane Pender) #2

Hi Ciecy,

there a couple ways to do this.

The simplest way would be:

set up a column on your spreadsheet called Running Total and put the formula in that column that (adds the previous running total + Debit - Credit ).

In the app, when you add a new transaction for a customer, your spreadsheet will calculate the running total and it should show up on the transaction in the app after you save and sync.

(Steven Coile) #3

@Mary_Jane_Pender, your idea would require an app resync after each transaction. That is most definitely not the simplest way.

Instead, a virtual column can maintain the running total:

=(sum(select(Transactions[credit], in([custID], list([_thisrow].[custID])))) - sum(select(Transactions[debit], in([custID], list([_thisrow].[custID])))))

Or, if credits and debits are represented as positive and negative numbers in the [amt] column:

=sum(select(Transactions[amt], in([custID], list([_thisrow].[custID]))))

(Mary Jane Pender) #4

I realize my suggestion requires the sync after and may not be the most elegant solution but I was concerned that over time the SUM expression could slow down as the transactions increased in number.

(Mary Jane Pender) #5

Ciecy, along with my suggested solution I should have also added that I would create an action that would do the sync.

Steven’s suggested solution would be OK if you do not expect to have hundreds of transactions per customer.