4 Replies Latest reply on Feb 19, 2016 11:05 AM by AllegroDataSolutions

    Sum vs Summary

    AllegroDataSolutions

      Two related tables. The child table contains records of transactions with fields for the transaction date, the name of the branch where the sale took place, and  the extended total amount of the sale. The parent table will contain fields showing the totals for each branch in the current year. The tables are linked by a code for the branch name and a calculated field containing the year of the transaction (matched to a calculation for the current year in the parent table).

       

      There seems to be two ways to calculate the year to date sales totals for each branch:

      • Using a summary field in the child table (which is filtered for the branch and current year by the relationship).
      • Using a calculated field in the parent table (in which the Sum function is used to add the extended totals for all matching records.

       

      I am wondering if there is any performance improvement or other benefit between the above techniques.

        • 1. Re: Sum vs Summary
          Mike_Mitchell

          Yes, there are differences. The summary field will typically be faster (although not tremendously so). The biggest advantage is you can put a single summary field in the child table and use it wherever it applies. For example, if you have the Sum calculation field in the parent table, it doesn't help you if you want to create a sub summary report on the child side. A summary field does. So it makes your solution a little leaner, which is a good thing.

          • 2. Re: Sum vs Summary
            BarbaraCooney

            How do you implement a "find" mode to allow for a user to find all orders where the total is greater than a user-entered amount? With a calc field in the parent, the user can enter their criteria in find mode. This isn't possible with a related summary field.

             

            PS: I suppose the "best" way is to actually have a stored number field "Total Order" in the parent that is set by script. But, let's ignore that for the purpose of this question.

            • 3. Re: Sum vs Summary
              Mike_Mitchell

              BarbaraCooney wrote:

               

              How do you implement a "find" mode to allow for a user to find all orders where the total is greater than a user-entered amount? With a calc field in the parent, the user can enter their criteria in find mode. This isn't possible with a related summary field.

               

               

              Yes, for that use case, you'll need to use the other method. Including this one:

               

              PS: I suppose the "best" way is to actually have a stored number field "Total Order" in the parent that is set by script. But, let's ignore that for the purpose of this question.

               

              I don't necessarily think it should be ignored; it's important for larger data sets, and allows for Find. But whether it's the "best" option depends on the data load vs. the time to implement in scripting vs. performance considerations.

              • 4. Re: Sum vs Summary
                AllegroDataSolutions

                I have used both methods in the past (the calculated, non-summary method, more frequently). But, typically, the solutions have been either relatively small or required a relatively small number of such calculations, so it was hard to gauge whether there was any performance difference between the two.