1 Reply Latest reply on Aug 21, 2014 11:36 AM by philmodjunk

    Problem with Subtotal on related data

    ruhlanma

      Title

      Problem with Subtotal on related data

      Post

           I've read a couple of posts that appeared to be similar in nature to my problem, but the solutions haven't made sense to me.  Here is my scenario described in simple terms:

            

           I have two tables:

                   Tbl1 = Customer

                   Tbl2 = Calls

            

           Any given customer can have multiple calls.  However, only the latest call is used in my subtotal.  I have a field in customer called latestcallid    I use this to set a one to one relationship between customer and calls.  It will always contain the call ID of the last call made.

            

           On each call, the rep records simple sales figures.  Product A = $250,  Product B = $100   So in the call table there is a field for productA and productB that contains these numbers.  There is also a field for point-of-sale .  This contains the name of the store where the customer bought that product.   I also created a summary field in this same table keeping a running total of ProductA based upon grouping by point-of-sale.

            

           I created a simple report layout that shows something like this:

            

           Store Name     Customer Name          Product A         Product B

           XYZ Store          John Smith                    $250                 $100

           XYZ Store          Joe Johnson                 $800                  $0

           Subtotal for XYZ Store                             $1050               $100

            

           My report layout is based on the customer table.  When it shows the subtotal line all it does is repeat the last customers numbers.   So instead of the report above, it instead looks like this:

           Store Name     Customer Name          Product A         Product B

           XYZ Store          John Smith                    $250                 $100

           XYZ Store          Joe Johnson                 $800                  $0

           Subtotal for XYZ Store                             $800                  $0

            

            

           I was startled to find that I had to even create these summary fields.  Seems a huge waste of storage.    But even after creating them I am not getting the desired result. 

            

            

            

        • 1. Re: Problem with Subtotal on related data
          philmodjunk

               Summary fields don't store any data. They calculated values on an "as needed" basis and aren't terribly different from an "aggregator" function used in SQL queries.

               A summary field defined in calls won't work for a layout based on Customer. You could turn this around by basing your layout on Calls and using find criteria to omit all but the most recent call. Then your summary fields in Calls will return the correct value.

               But you can set up a Summary field in customer and use some calculation fields defined in customer for your summary fields to summarize. A calculation field for Product A would simply be an unstored calculation field that copies the value of this field from the most recently made calls record for this customer. Then a summary field can summarize this calculation field.

               PS. using separate fields for each product all as part of the same record isn't the most flexible data model to use. It has clear advantages for your current set up, but also some major disadvantages as well. A related table with one record for each type of product that links to both a table of calls records and a table of products records can in many cases be a much more flexible approach. For one thing, if you discontinue Product A and add 3 new products, you don't have to make a major redesign of your database as most of the work, if not all would become a data entry task rather than a system design change.