Total Account Balance using Debits and Credits

[ronbrumbarger]

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!

[CoopperBackpack]

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. 

[ronbrumbarger]

THANK YOU! This was most helpful. Sorry for my late response...

[NailDyanC]

Just to add to this post, you may also consider checking this workaround: 

 

[ronbrumbarger]

Thank you @NailDyanC!!

[NailDyanC]

On 1/23/2022 at 2:50 PM, ronbrumbarger said:

Thank you @NailDyanC!!

Hello @ronbrumbarger, just want to add , you may also consider checking this article if you still need that workflow: https://howto.caspio.com/tech-tips-and-articles/advanced-customizations/calculate-a-running-total-in-your-report/