Jump to content
  • 0

Total Account Balance using Debits and Credits



I'm always amazed at how many smart people are on here... Hopefully one of you have a minute to help me out...

I have a table with two UserIDs: DebitID and CreditID (think the perspective of your bank - debits to your account reduce your balance). The table also has an Amount field. The logged in user (authfield:UserID) may have sent (Debit) or received (Credit) an Amount. If they sent money, the DebitID field equals their UserID and User's ID to whom they sent the money would be identified in the CreditID field.

My challenge is I need the SQL statement to generate a "total balance" from all transactions where... Here's my JS kind of equivalent code.

var AccountBalance = 0;
if authfield:UserID == field:DebitID
  AccountBalance = AccountBalance - Amount
else if authfield:UserID == field:CreditID
  AccountBalance += Amount

I hope ^ is legible and one of you smart people out there can lend a hand. Thanks - Stuck!

Link to comment
Share on other sites

5 answers to this question

Recommended Posts

  • 0

Hello @ronbrumbarger,

If my understanding is correct, then the workflow is the following:

1) For example, I am logged in as a user with UserID = 1.

2) In the first record we can see that I sent $100 to the user with UserID = 2

3) In the second record we can see that the user with UserID = 3 sent $250 to me.


If this description is correct and you need to display the value as Total, then you may add Totals&Aggregations in a Report DataPage, set it to 'Formula' to use your custom formula:


The idea is to sum all the Amount values from the table where CreditID = logged in UserID and subtract the sum of all the Amount values from the table where DebitID = logged in UserID.

Please use your fields/table names. You may also apply formatting to the result. 

IsNull((SELECT SUM(Amount) FROM Bank_balance WHERE CreditID = '[@authfield:ID]'),0) - IsNull((SELECT SUM(Amount) FROM Bank_balance WHERE DebitID = '[@authfield:ID]'),0)

Hope this is the expected result. 

Link to comment
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.

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.

  • Create New...