Announcement

Collapse
No announcement yet.

Calculate running balance for checkbook

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Calculate running balance for checkbook

    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
    Originally posted by jackjeep4 View Post
    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!
    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.
    Albert Drent
    aducom software netherlands
    scriptcase partner, reseller, support and (turn-key) development
    www.scriptcase.eu / www.scriptcase.nl

    Comment

    Working...
    X