7 Replies Latest reply on Jul 24, 2012 8:34 AM by Malcolm

    What is the difference between Sum(child::x) and child::SumOfX?

    Malcolm

      This is a technical question and I'm hoping to get some feedback from some of the engineers.

       

      A typical invoice has a 1:N relationship between invoice and line items.

       

      Let's say we have two fields in Line items, Cost and TotalCost, which a summary of Cost.

       

      Invoices often have a few extra bits and pieces applied, like a discount, so it's necessary to bring total from the line items into the Invoices table. That can be got easily by two methods:

       

      1. Sum(Line items::Cost)

      2. Line items::TotalCost

       

      My question is, what is the difference between these methods? They appear to produce the same result. Do they use the same methods and are they completely interchangeable or are there some esoteric conditions where one or the other is required?

       

      Malcolm

        • 1. Re: What is the difference between Sum(child::x) and child::SumOfX?
          wimdecorte

          architecture-wise I would argue that "total cost" is an attribute of the invoice and not of the line item.  I certainly would not use a summary field in the line items to calculate the total cost of all items on the invoice.

           

          Given that "total cost" is going to feature on many reports I would avoid making it a calculated field but would choose for the value to be set through events.  That would make it a simple number field that will be extremely fast to display, sort, sum on AR reports etc.

          • 2. Re: What is the difference between Sum(child::x) and child::SumOfX?
            taylorsharpe

            They are the same in function.  I have always wondered if one was faster than the other, but I've always been more a fan of Sum(Line Items::Cost) because it seems more intuitive to me.  Does anyone have the FMBenchmark tool and want to run a bunch of these through to see which function is faster? 

            • 3. Re: What is the difference between Sum(child::x) and child::SumOfX?
              Malcolm

              architecture-wise I would argue that "total cost" is an attribute of the invoice and not of the line item.  I certainly would not use a summary field in the line items to calculate the total cost of all items on the invoice.

               

              Good points Wim but you are not answering my question.

               

              Malcolm

              • 4. Re: What is the difference between Sum(child::x) and child::SumOfX?
                AlanStirling

                Hi Malcolm

                 

                Which is faster?

                 

                1.  Sum(Line items::Cost)

                2.  Line items::TotalCost

                 

                Unless you know better, I think that method 2 could prove to be unreliable, in which case any speed benefit is worthless.

                 

                Without a GTRR in front of it, the result could vary from the total of one line item's record, to the sum of them all.  Nothing in this line of script defines the found set in Line Items.

                 

                It's just going to look at the first matching record for Line Items and then see what the current found set of Line Item records add up to. It is most probable that you have already selected the Line Item records that relate to the invoice, but if for some reason the found set is different, then the Total Cost will be wrong.

                 

                When using the first formula, you are selecting the records to be totalled via the relationship, before totalling them. I find this method the more natural one - I can trust that it will work every time, without the necessity for supporting script steps.

                 

                I have to say, since I always use the first method, I can't provide practical experiences of any issues with the second method, but I just think it could produce the wrong result sometimes.

                 

                Furthermore, why not use one script step instead of two (or more)?

                 

                Best wishes - Alan Stirling, London, UK

                • 5. Re: What is the difference between Sum(child::x) and child::SumOfX?
                  comment

                  Malcolm wrote:

                   

                  My question is, what is the difference between these methods? They appear to produce the same result. Do they use the same methods and are they completely interchangeable or are there some esoteric conditions where one or the other is required?

                   

                  Probably none of the above. Historically, before version 7, related summaries didn't work at all. Later it was discovered (since the matter was not documented) that they have been "enabled" and that their performance is improving with each version (there were terrible refresh issues in the early versions, and I am not sure they are all fully solved by now).

                   

                  Now here comes the interesting part:

                   

                  In the help for up to and including version 10, under "Summarizing data in portals", you'd find the following (emphasis is mine):

                   

                  1. Create a calculation field in the table in which you want the summary to be displayed.

                  2. For the calculation field, define a formula that includes an aggregate function for the type of summary calculation you want to perform.

                   

                   

                  However, starting with version 11, the same section reads:

                   

                  1. Create a summary field in the table that related records are being displayed from.

                  ...

                  Tip  For a more complex summary of your data, use a calculation field to define a formula.

                   

                  I don't know what they mean by "a more complex summary", but it's worth noting that no equivalent of the List() function exists as an option for a summary field,

                   

                  My conclusion from all this is that we are being gently steered away from aggregate calculations and towards related summary fields, but the process is still not finished.

                  1 of 1 people found this helpful
                  • 6. Re: What is the difference between Sum(child::x) and child::SumOfX?
                    Malcolm

                    I'm not focussed on speed alone, although that is interesting to me. I'm primarily interested in the difference in functional differences. For instance, in general, Sum() is able to take fields from different tables so it has far greater utility than the summary field. But, in the narrow case given by the example, are the two items functionally equivalent?

                     

                    1.  Sum(Line items::Cost)

                     

                    2.  Line items::TotalCost

                     

                     

                     

                    Unless you know better, I think that method 2 could prove to be unreliable, in which case any speed benefit is worthless.

                     

                    Without a GTRR in front of it, the result could vary from the total of one line item's record, to the sum of them all.  Nothing in this line of script defines the found set in Line Items.

                     

                    In this example, item 2 Line items::TotalCost is a summary field. If you are on a layout based on the Line Items table you are correct. It is necessary to establish a found set and possibly, a sort order, to get the correct results.  When you are on a layout which has a relationship to Line Items then the found set is implicit in the relationship, as is, perhaps, a sort order. As a consequence, the correct result is obtained.

                     

                    Unless an extra field is required it is unnecessary to create a calculation field in related tables. You can simply place the related summary field onto the layout to display the results.

                     

                    Beatrice Beaubien and Michael Horak recently showed me that if you place the related summary field onto the layout within a single row portal which uses portal filtering the filtering is respected by the summary field. This is, effectively, providing the same role as a subsummary part. After some thought, this behaviour should be expected. Portals provide the conditions and context required to resolve a summary field calculation. The filtering is the same as doing a different find in a subsummary report.

                     

                    A portal also allows a summary field with a running count to be displayed correctly.

                     

                    Malcolm

                    • 7. Re: What is the difference between Sum(child::x) and child::SumOfX?
                      Malcolm

                      Very interesting, thanks Michael!

                       

                       

                      Tip  For a more complex summary of your data, use a calculation field to define a formula.

                       

                      I don't know what they mean by "a more complex summary", but it's worth noting that no equivalent of the List() function exists as an option for a summary field,

                       

                      I guess that a calculation may contain much more than a sum() statement. As Wim pointed out earlier, line items are simply a way of handling the data, making it possible to describe a compound object. The parent record is the thing and it may carry data which will modify the sum() of the line items.

                       

                      Malcolm