6 Replies Latest reply on Jun 27, 2017 11:26 AM by philmodjunk

    how can I get a Sum of a calculation per record

    ericjlindholm

      my apologies in advance for how bad I am at communicating my ideas.

       

      is there a clean way way to get get the sum of a calculation per record without creating a field with that calculation in that table? 

       

       

       

      current example requires a field that contains the result of the calculation - sum ( child::qtyXpricePer)

       

      desired example that i know does not work- Sum ( child::qty * child::pricePer )

       

       

      I could do something like loop through the child records, calculate a variable and sum that but I am hoping for something cleaner then that. 

        • 1. Re: how can I get a Sum of a calculation per record
          gofmp15

          Check out getsummary(field;xxxx) That might work on a selection of sorted records.

          • 2. Re: how can I get a Sum of a calculation per record
            philmodjunk

            is there a clean way way to get get the sum of a calculation per record without creating a field with that calculation in that table?

             

            Why do you wish to avoid adding that calculation field? Just seeking a more complete picture here.

            That "line item cost" calculation is a very common thing to put into such a table.

             

            ExecuteSQL ( "

            SELECT Sum ( qty * pricePer ) FROM child

            WHERE

                 ForeignKey = ? ;

            "" ; "" ; Parent::PrimaryKey )

             

            But I'm not sure that the above use of Sum ( Expression ) is valid syntax in ExecuteSQL

            1 of 1 people found this helpful
            • 3. Re: how can I get a Sum of a calculation per record
              ericjlindholm

              I am looking to improve my performance over WAN by reducing unstored calculations.

               

              my invoice records in this case have cost summary fields that are populated with a script when the invoice layout is close or a cost analysis popover is opened .   this seriously improved my performance. 

               

               

              what I would really like this to do is not just do the qty * costPer from the same record but actually pull the price from a related record such as child_inventory::costPrice

               

              ExecuteSQL might just be the ticket.

              • 4. Re: how can I get a Sum of a calculation per record
                philmodjunk

                What you describe is a stored calculation as both fields are in the same record. Thus, this should not greatly affect performance. And displaying the line item cost is very frequently needed anyway as the user fills in an order.

                • 5. Re: how can I get a Sum of a calculation per record
                  gofmp15

                  Note sure if this is applicable:

                   

                  Using a script to put the cost/price of an item pulled from the pricing table into the record while using calculated fields to do the math is OK.

                   

                  Price = set by script pulling the amount from appropriate table.

                  Qty = Manually set or set by script

                  Total = calculated field Price x Qty

                   

                  Since these are item records related to parent invoice record

                  the invoice record would have

                  Subtotal = Sum(items:Total)

                  Tax = scripted by amount from tax amount table

                  Total = Calculation of subtotal + tax

                   

                  I always used scripts rather than calculated fields since these numbers should not change due to things like damaged or deleted item records. If the script totals were different from what is now displayed, then something went wrong.

                   

                  A verification field using calculations would be used to compare the scripted totals with itself.

                  • 6. Re: how can I get a Sum of a calculation per record
                    philmodjunk

                    Scripts and calculation fields aren't the only options. An auto-entered calculation would work perfectly here.