1 Reply Latest reply on Jul 22, 2014 2:30 PM by philmodjunk

    ORDERED THE SUM

    kconick

      Title

      ORDERED THE SUM

      Post

           hi, 

           from the table: 

            

           name       variable   value

           record1          x          1

           record2          y          3

           record3          x          1

            

           I would like to display in two separate fields the sum of the values of the records with variable x and y to obtain: 

            

           x = 2 

           y = 3

        • 1. Re: ORDERED THE SUM
          philmodjunk

               You can use a variety of methods as this is yet another example of the "sum if" problem.

               You can set up two one row filtered portals with a summary field that sums the value field as the only field in the portal row. The relationship can match to all records or a sub set. The portal filter then specifies a specific value in the variable field so that one portal only allows a value of "x" in the variable field and the other specifies "y".

               Two calculation fields can be defined that use ExecuteSQL with the Sum function and the appropriate WHERE clause can also produce these same totals.

               And there are ways to set up relationships that match to only records with a variable field value of x and another that only matches when the value of the variable field is y and then a summary field or the sum function can compute this same total.

               Other methods are also possible.