Jump to content
  • 0

Running Balance per Transaction


Go to solution Solved by Benades,

Question

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.

 screenshot-c1dcn529.caspio.com-2021_04.16-15_16_40.png.fbffd24cc9a58db2fbcabf2c4f7bc3d8.png

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.

 

Link to post
Share on other sites

6 answers to this question

Recommended Posts

  • 0
  • Solution

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

 

Link to post
Share on other sites
  • 0
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.

Link to post
Share on other sites
  • 0
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!

Link to post
Share on other sites
  • 0
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?

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...