Running Balance per Transaction

[Benades]

I am trying to create a log sheet of sorts that shows the users transaction history. I have the table (as below) showing different transactions debit for income and credit for expenses but I want the balance column to show how the balance is increasing or decreasing as the transactions are coming in.

 

ie User makes a deposit of 1000 and the balance also displays 1000, but next line he has an expense in the Credit column of 100 so balance should reflect 900. The table houses multiple users as seen above so that another users credit or debit doesn't influence other users balances.

 

[Benades]

I have found my solution below and just tweaked it to my application

(SELECT SUM(IsNull([@field:Debit],0) - IsNull([@field:Credit],0)) FROM tbl_user_trans_hist WHERE user_ID= target.[@field:user_ID] AND Date <= target.[@field:Date])

 

[NailDyanC]

Hello @Benades, you may try to check the suggested workaround in this link about creating running balance: 

 

[Benades]

Hi @LittleMsGinger, I saw your input from the above mentioned post and seems to be the best working one, but I cannot figure it out because the SQL jargon for the calculated fields are just foreign to me. Would you mind giving some input here?

[cheonsa]

On 4/19/2021 at 7:22 AM, Benades said:

Hi @LittleMsGinger, I saw your input from the above mentioned post and seems to be the best working one, but I cannot figure it out because the SQL jargon for the calculated fields are just foreign to me. Would you mind giving some input here?

Hello @Benades,

I am assuming that you were trying the formula in a Formula field. Unfortunately, you cannot use this in a Formula field due to system limitation. Select Statement is not applicable in table level. You may use this in a Calculated Field in Tabular Report.

 

If you need it to be saved in the table as well, it needs Triggered Action to be implemented.

[cheonsa]

On 4/20/2021 at 8:50 AM, Benades said:

I have found my solution below and just tweaked it to my application

(SELECT SUM(IsNull([@field:Debit],0) - IsNull([@field:Credit],0)) FROM tbl_user_trans_hist WHERE user_ID= target.[@field:user_ID] AND Date <= target.[@field:Date])

 

In addition to my previous response, it seems that the modified formula will work as expected.

Hope this helps!

[Benades]

On 4/24/2021 at 8:23 AM, LittleMsGinger said:

If you need it to be saved in the table as well, it needs Triggered Action to be implemented.

Hi @LittleMsGinger, How do I go about doing this?
I have implemented the formula in a Calculated field in a Datapage but I also need it inside the table because it needs to be called from other tables.

So how do I setup a trigger to fetch that result or even better to calculate itself?

[cheonsa]

Hello @Benades,

I found this post also here in forums that you may use for refence in creating Triggered Actions:

 

[NailDyanC]

Hello, just want to add what I have found. This is Caspio howto article about running total that might help: https://howto.caspio.com/tech-tips-and-articles/advanced-customizations/calculate-a-running-total-in-your-report/