Running balance without trigger

[Harbinger]

Is there a solution to have a total running balance in Caspio?

for instance:

Fix Budget = $1000

Budget          Allocated           Running balance

$1000          $200                     $800

                        $150                     $650

                        $250                     $400

                       $100                      $300

 

The initial value from allocated amount will be subtracted from the Fix Budget then the difference will be put under Running Balance column.

But the next allocated amount should be minus from the current running balance then the difference will be put again under Running Balance column and so on.

Would there be any work around on this? I have seen one from here but that didn't solve my problem.

Any thoughts?

 

~ H

[kpcollier]

Hey @Harbinger, I have similar workflow for my Orders/Receiving app. When an order is received, Quantity_Received becomes Initial_Quantity_Stored. I did this with a calculated value, it was easy. From there, I have a Qty_Sent and Total_Qty_Sent. The reason I think this is similar is because I start with initial quantity (Budget). The user enters in the Qty_Sent for how many items they are sending out of our shop. Qty_Sent is added up every time in the back-end and stored with Total_Qty_Sent. I then created another field for Quantity_Remaining (Running balance) which is a formula of Initial_Qty_Stored - Total_Qty_Sent . 

If Qty_Received is 10, then thats what our Initial_Qty_Stored starts at. Say an employee sends 4 of those units. He would enter in 4 at Qty_Sent. He would now see the updated Qty_Remaining at 6. He would go in again and enter in 2 this time. That would be added to Total_Qty_Sent again and the Qty_Remaining would be 4.

In your case I would start with a field that gets your Budget. I would then create a field for Total_Budget_Spent (see trigger for usage). I would then create a field for Budget_Spent (for the money allocated). Finally, I would create a field for Balance (Set to formula using [@field: Budget] - [@field: Total_Budget_Spent]).

Here is the trigger for Total_Budget_Sent.

 

If you need more help or understanding of this workflow, don't hesitate to message me.

[Harbinger]

Hi @kpcollier

Thank you for the triggered action approach

I've been hoping not to use a triggered action as there would be affected conditions in my tables, tho I really appreciate your help.

So probably, some JavaScript or formula field approach can help.

~ H

[cheonsa]

Hi @Harbinger,

You can use this formula in the calculated field:

(SELECT SUM(IsNull(Balance, 0)) FROM Running_Balance WHERE Code= target.[@field:Code] AND ID <= target.[@field:ID])

Hope this helps!

[Harbinger]

@LittleMsGinger Thanks for the idea. Can you elaborate on how to implement this exactly? Thank you!

[sandy159]

Hi All, 

Just wanted to add that there is a How-To article about running balance available now: Calculate a Running Total in Your Report