6 Replies Latest reply on Oct 17, 2016 5:29 AM by philmodjunk

    Native calculations outside the found set

    Stu412

      Hi there

       

      I have a data table of around 20,000 records which stores sales data for several hundred customers.  Each customer has around 100 rows of sales data.  There are various criteria which are needed as well in order to allow summing and sorting on a row by row basis.

       

      I need to be able to show the customer sales data for say product XYZ against the national average.  So far, this has involved me doing the following:

       

      • Search for customer in question by their ID and return only their records, creating a 200 row found set
      • Use native Filemaker Case and Summary commands to split out and summarise the data on a row by row basis
      • For the national averages and querying the 19,800 records remaining, I've needed to use ExecuteSQL as this is not limited by what's in the found set, but it's amazingly slow.

       

      Is there any way that I can use Case and Summary fields to search and return figures on the remaining 19,800 records so the results can appear alongside the customer's own results?  As these are outside the found set for this customer, it appears I cannot reach them to calculate on them.

       

      Thanks in advance

        • 1. Re: Native calculations outside the found set
          Johan Hedman

          Create Statistic fields for Total and Average.

           

          Then create a new List layout and add a Part for Customer.

               Put Customer name and Total and Average on that Part.

               Delete the Body Part

          Sort your records on Customer and what you will see is summarised Total and Average for each Customer

           

          If you like to add total for all Customers, add a Trailing Grand summary and add all those fields again

          • 2. Re: Native calculations outside the found set
            Stu412

            Hi Johan

             

            Thanks for this - what I need per row would be something like:

             

            Customer A

             

                                Customer Price               Average of all other customers prices

            Product X     500.00                              550.25

            Product Y     250.00                              298.10

             

            This compares the actual price of product X for customer A against the overall average price for product X for all customers A-Z (where customers B-Z are not already in the found set).

             

            Thanks

            • 3. Re: Native calculations outside the found set
              Johan Hedman

              Then you just add a second Sub Summary Part for Product.

               

              In your sort you add after Customer, Product

               

              This will create following nice summarised report

               

              Customer - Total and Average

                   Product per Customer - Total and Average

              Trail Summary

              • 4. Re: Native calculations outside the found set
                Johan Hedman

                I also recommend you to read the free FileMaker Training Series

                http://www.filemaker.com/learning/training/fts.html

                • 5. Re: Native calculations outside the found set
                  Stu412

                  Hi Johan


                  The reporting for me is fine with the parts and sorting etc.  I need to get the overall average of 20,000 and show it on this report on a per row basis, for example:

                   

                  Records 1 - 3 in found set might be as such:

                   

                  ID Detail          Value       National Average

                  1 Product X   500.00     500.00           <<< Incorrect as it does not include values from 2 and 3 outside of found set.

                  ---------------------------------------------------

                  2  Product X   575.37   <<< Not shown on report as separate row from another customer, used only for calc

                  3  Product X   575.38   <<< Not shown on report as separate row from another customer, used only for calc

                   

                  In the example above, I need the national average to actually show 550.25.  However, because records 2 and 3 are outside of the found set (by virtue of the initial script I run), they will not be included in the summary average calc (hence the use of SQL to find and include them).

                   

                  Hope thats somewhat clearer.

                  • 6. Re: Native calculations outside the found set
                    philmodjunk

                    I suggest using a script to find those records for the national average, compute the needed statistical values via your summary fields and then store them into a different record in indexed number fields. You can then include them via relationship in your regular report.

                     

                    You could just use a self join relationship and reference your summary fields, but this can be very slow if you have lots of records. If you pre-calculate and store the values, you only need calculate the values once until the next time you need to update them.