Announcement

Collapse
No announcement yet.

New Calculated field for each row.

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

  • New Calculated field for each row.

    Hello SC users,

    First of all, please note that I'm pretty new to all of this.

    Right now our company has a working application using a MySQL database and I'm trying to create reports for this app as a separate app. I'm using Grid.

    The table I have is as follows: ID, value1, value2, value3 etc..

    What Im trying to do is add a calculated field at the end of the table, that will be showing (value1 + value2 - value3), for each row - its own value.

    So what I did so far is: I've created a new field "total" and entered the following sql statement for it:

    SELECT value1 + value2 - value3
    FROM mytable

    what it does - it shows the correct calculation but on each row it puts this calculation for all rows. (a list of all of them in one field)

    Example: Lets say I have 3 records in mytable, using the SELECT value1 + value2 - value3 FROM mytable I get the following:

    ID value1 value2 value3 total
    1 25 30 5 50
    45
    40
    2 35 20 10 50
    45
    40
    3 45 35 40 50
    45
    40
    and I obviously want it to be like that:

    ID value1 value2 value3 total
    1 25 30 5 50
    2 35 20 10 45
    3 45 35 40 40
    Please advise. Im on SC version 8.

  • #2
    You do not need to bother with SQL for that.

    Use your "total" custom field, but do not assign it any SQL or anything. Instead, in the grid's OnRecord event add PHP:

    Code:
    {total} = {value1} + {value2} - {value3};
    This will for each record take that record's field values and and perform that calculation.

    Comment


    • #3
      Originally posted by adz1111 View Post
      You do not need to bother with SQL for that.

      Use your "total" custom field, but do not assign it any SQL or anything. Instead, in the grid's OnRecord event add PHP:

      Code:
      {total} = {value1} + {value2} - {value3};
      This will for each record take that record's field values and and perform that calculation.
      Thank you very much! It worked like a charm!

      I'm on the trial period for 2 days only and I already have full customizable reports for our DB! And I'm yet to dive into any other features of ScriptCase. It's an amazing piece of software! Im definitely buying it.

      Comment


      • #4
        Let us know if you need anything elese! We are glad that you are enjoying Scriptcase.
        Best regards,
        Carlos Lacerda.
        ScriptCase Commercial Manager.

        Skype: carlos.lacerda82
        Email: carlos@scriptcase.net
        Visit our Blog: http://www.scriptcase.net/blog/
        Visit out fan page: http://www.facebook.com/Scriptcase

        Comment


        • #5
          You are most welcome - just as an extra clarification - note that you wrap variables in {curly brackets} to tell SC to use the values in that app's fields (as listed under the Fields tree item on the left).

          If something is in [square brackets] it's a global variable, whilst $variables are local to that event

          Comment


          • #6
            Hi Ruslan,
            I got a similar problem on V8
            I have added a simple formula {TotalRow} = ({UnitPrice} * {Quantity}); on Ajax Events of Quantity_OnChange in order to refresh the total once the user change quantity field.
            UnitPrice and Quantity are decimal
            The total now is 100 times the correct calculation .. I'm really stucked here.
            Did you understand what to do to have a right calculation ??
            Thanks

            Comment


            • #7
              What type is TotalRow?

              Plus, for all 3 fields, check the fields' General settings | Values Format.... these can affect how the number is represented. Pic below is for Decimals too, to 2 decimal places....

              Capture.JPG

              Note: Maximum size of 5 allows "23.75" (incl decimal point).

              Comment


              • #8
                Ok Thanks - Sorry for duplication but I was in great ... hurry !! ;-))

                Comment


                • #9
                  :-) understood

                  Comment

                  Working...
                  X