Announcement

Collapse
No announcement yet.

Running Balance in Grid

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

  • Running Balance in Grid

    Can anyone recommend how to perform a running balance in a Grid application as a calculated field?

  • #2
    Originally posted by bengrech View Post
    Can anyone recommend how to perform a running balance in a Grid application as a calculated field?
    It's the sum of a column? And the column is a calculated field? (sorry if this is a stupid question). Within a row you can perform a script to calculate a new field by using a onrecord event. This will trigger for each and every column. This calculated column can be summed by using the summary and totals secion left in the menu.
    Albert Drent
    aducom software netherlands
    scriptcase partner, reseller, support and (turn-key) development
    www.scriptcase.eu / www.scriptcase.nl

    Comment


    • #3
      What I would like to achieve is the following

      Name, Paid, Running Balance
      A, 10, 10 (10+0 since first row)
      B, 20, 30 (10+20)
      C, 50, 80 (30+50)

      Comment


      • #4
        Originally posted by bengrech View Post
        What I would like to achieve is the following

        Name, Paid, Running Balance
        A, 10, 10 (10+0 since first row)
        B, 20, 30 (10+20)
        C, 50, 80 (30+50)

        Create a new field RB. In the onscriptinit set a global variable [glob_old]=0;. In the onrecord event: [glob_old]=[glob_old]+{Paid}; {RB} = [glob_old];

        That should do it.
        Albert Drent
        aducom software netherlands
        scriptcase partner, reseller, support and (turn-key) development
        www.scriptcase.eu / www.scriptcase.nl

        Comment


        • #5
          Hi Albert,

          Your idea is good but It calculate balance only for one page. It is not give running balance for all pages. If you put the [global_old]=0; in onApplicationinit instead of onScriptinit.it will calculate running sum for all pages. But this time the problem is the changing pages(prev, next navigation). It will calculate and add running total again and again without stopping. Therefore It gives wrong runnging balance.

          I am thinking that how this will be solved ?
          Thank you.

          Comment


          • #6
            Hi,
            you have to solve this with sql.
            Go to the SQL section, in the SQL Preparation field type: @bal = 0
            Add the following line to your SQL statement (field selection): @bal := @bal + paid as balance

            That's it.

            jsb
            Last edited by jsbinca; 01-11-2014, 10:51 AM.

            Comment


            • #7
              Many thanks jsbinca will give it a try!

              Comment


              • #8
                Hi jsbinca,

                Yes, your code works and calculate running balance total for only each one page(only current shown page). It does not caltulate running balance total from first page to end page.

                Thank you.

                Comment


                • #9
                  Hi,
                  are you doing anything else with your data?
                  I have a grid with appr. 200 pages (50 records a page) and it is calculated from the first to the last page.
                  Try adding a ORDER BY balance to your SQL-Statement.

                  jsb

                  Comment


                  • #10
                    Hi jsbinca and Albert,

                    Yes, It is working now properly after adding ORDER BY.

                    Thank you both of you again.

                    Comment

                    Working...
                    X