Account Running Balance

[Mrudi]

Looking to create a running balance by date. 

Table is a list of transactions by date with an amount for each.  Both negative and positive values.  Want to keep a running balance.  Thoughts on an approach?

[kpcollier]

You can create a tabular report datapage. You can include a search box to filter by date or more if you want to. On the Results page of the datapage wizard, click on the field picker on the bottom and add in Totals & Aggregation. You can pick the field/column to use in the totals, which would be your transaction amount. Set this to Sum and you will have calculated total on the bottom of the report.

[IvanCarlson]

On 11/27/2019 at 9:13 AM, kpcollier said:

You can create a tabular report datapage. You can include a search box to filter by date or more if you want to. On the Results page of the datapage wizard, click on the field picker on the bottom and add in Totals & Aggregation. You can pick the field/column to use in the totals, which would be your transaction amount. Set this to Sum and you will have calculated total on the bottom of the report.

What if you want to show a sum accumulating total on each transaction line item

[NiceDuck]

If you are not fond of multiple records display on your screen, I can also suggest that you create a dummy table and task triggers to calculate those values for you. Therefore, you will have a table which has the summary report of your transactions table.

[kpcollier]

On 11/29/2019 at 9:59 AM, IvanCarlson said:

What if you want to show a sum accumulating total on each transaction line item

In the same tabular report, you can insert a Calculated Field column and use a formula to calculate the sum on each line item. These formulas are very versatile. If you are experienced with with JS, using an HTML block and using JS to do the calculations is a liiiiiiittle faster than using Calc Fields. Should only matter if you use a lot of them. 

[cheonsa]

Hi @Mrudi,

If you don't want to use Triggered Actions and you just want to display the running balance in the reports only, you can use a calculated field instead. 

You may use this formula:

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

 

Balance is the name of the field where I store the amount and Running_Balance is the name of the Table. You may change these names based on your corresponding field names.

 

Hope this helps!

[Meekeee]

Hi! Just an update on this, Caspio has a Tech tip called Calculate a Running Total in Your Report. To know more on how to implement it in the DataPage, you can check it here: https://howto.caspio.com/tech-tips-and-articles/advanced-customizations/calculate-a-running-total-in-your-report/

[Lynda]

This example DOESN'T WORK! I followed it exactly and after working on it for a frustratingly three hours I could not get it to work. I kept getting an invalid formula. I tried it with a Tabular data page, I tried it with a Report and Chart datapage (which is where I need it). I recreated the table that the example used and still could not get it to work.

Has anyone made this work?

Lynda

[GoodBoy]

Hello! Perhaps you can try the similar formula in this article - https://howto.caspio.com/tech-tips-and-articles/advanced-customizations/calculate-a-running-total-in-your-report/  This should work!

[Flowers4Algernon]

Hello this post may be helpful, too!: