4 Replies Latest reply on Aug 4, 2015 8:23 AM by Stu412

    How to get efficiencies on summary calculations

    Stu412

      Title

      How to get efficiencies on summary calculations

      Post

      Hi there

      I have a main data table which at the moment has 70,000 records, with groups of around 500 records attributable to unique customers.  I have the CustomerID_FK on the data table to facilitate the join so that I can show data relating to the customers on customer based layouts.

      Due to the nature of the requirements, I have quite a lot of summary fields based around various conditions - grouping, dates, percentages etc.  It's getting a bit slow to calculate this when I change customers.

      This may sound dumb, but is FM reviewing all 70,000 records before settling on the 500 or so I need in order to carry out the summary calcs I have?

      Can I improve this so I just have the 500 only, perhaps by an entry script?  I had a quick attempt at this using a find but it made no difference.

      Apologies for a fluffy question - it's more of a 'How does FM do it' rather that 'How do I do it' type of thing.

      Thanks

       

        • 1. Re: How to get efficiencies on summary calculations
          philmodjunk

          There are a number of possible factors involved. To start, how is your layout designed? Is this a list view layout based on the main data table, a list view layout based on the customer table or a form view layout based on the customer table?

          The total number of summary fields, the speed of your network connection, if any, whether you are doing this with FileMaker Pro or FileMaker GO (on an iPad or iPhone) can all make a signficant difference. So might the presence of conditional formatting on various layout elements.

          One useful detail to share is whether the data in your main table is static or dynamic. Static data is data such as Sales Invoices that, once delivered to the customer are never permitted to be edited. Dynamic data consists of records where any given record might be edited at any given time. The closer your records are to the "static" example, the better a certain trick of "Precalculating" and storing summary values becomes a way to achieve pretty dramatic improvements in speed.

          • 2. Re: How to get efficiencies on summary calculations
            Stu412

            Hi Phil

            The layout is based on a form using the customer details with the data being pulled from the data table onto the customer table.

            The data is pretty dynamic - FM will look at the date of a particular customer subset of records, decide where that falls within the other dates for the customer and then place it in a calculation field using CASE statements to evaluate the dates/years etc.  These calculations end up on the customer layout.

             

            • 3. Re: How to get efficiencies on summary calculations
              philmodjunk

              I am guessing, then, that you have placed summary fields from the main data table on this form view layout.

              but this general description:

              FM will look at the date of a particular customer subset of records, decide where that falls within the other dates for the customer and then place it in a calculation field using CASE statements to evaluate the dates/years etc.  These calculations end up on the customer layout

              Sounds like a major contributing factor. Does this describe a relationship using inequalities or are you implementing this in some other fashion? Can you provide an example of how this works? Perhaps you have a filtered portal here?

              • 4. Re: How to get efficiencies on summary calculations
                Stu412

                Yes, I was using filtered portals but due to the date sorting requirements they became unreliable (long story...).  I've been able to place summary fields from the data table directly onto the customer layout table.  However, each summary field requires only specific values to make it's total.  Therefore each record has several 'filters' if you like that I've added to set true/false conditions and allow/disallow a value into a summary column (as it appears on the table layout of the data table), so there's a lot going on.

                One thing I've just noticed.  Does FM cache this info?  Example:  

                I load a customer layout table with the summarised values displayed, move to the next customer, then the next 3 or 4 or so and it lags between customer loads.  However, when I flick back and forth between these customers, still on the data display tab, the lag has gone.  This is interesting behaviour, implying there is a cache or something similar (I'm speculating) which can hold the data in it's calculated format.