Calculate running balance for checkbook

Scriptcase 8 Discussion
#1

I am setting up a grid form and would like to keep a running balance after each transaction. It should look like this:

ID Date Income Expense Balance
1 3/17/15 $100 0 $100
2 3/18/15 $50 0 $150
3 3/18/15 0 $75 $75
4 3/19/15 0 $25 $50

I was able to create run sql statement to do this but I am not sure how to apply it in Scriptcase:

select id, date, expense O.income
,(select sum(income) - sum(expense) from ledger
where id <= O.id)
‘Balance’
from ledger O

Is this the best way? Can I calculate with onRecord?

Any help is greatly appreciated!

#2

[QUOTE=jackjeep4;34385]I am setting up a grid form and would like to keep a running balance after each transaction. It should look like this:

ID Date Income Expense Balance
1 3/17/15 $100 0 $100
2 3/18/15 $50 0 $150
3 3/18/15 0 $75 $75
4 3/19/15 0 $25 $50

I was able to create run sql statement to do this but I am not sure how to apply it in Scriptcase:

select id, date, expense O.income
,(select sum(income) - sum(expense) from ledger
where id <= O.id)
‘Balance’
from ledger O

Is this the best way? Can I calculate with onRecord?

Any help is greatly appreciated![/QUOTE]

You can calculate in the onrecord event. To store the result of your calculation you need to create a new field and apply the code into the event. I.e. if you have the customfield ‘delta’ you could write something like:

{delta}={income} - {expense};

But your query is using multiple tables so the most easy way is using ‘as fieldname’ to help the Scriptcase parser. Another option is to create a view with your query and then the sql is just a ‘flat table’ for scriptcase. I can’t tell what’s the best option, it depends.