4 Replies Latest reply on Oct 14, 2016 11:44 AM by philmodjunk

    Summarising pop up message

    Stu412

      Hi there

       

      A question regarding summaries.   I have a data table consisting of values and their associated customerID_FKs

      I have a customer table consisting of customer names and CustomerID_PK.  These are joined by the customer IDs and of course, there are many more fields besides those.

       

      What I need is 10 summary data fields from the data table to show on the customer layout.  The 10 fields are based on the same initial field, but split based on a certain criteria in a case statement:

       

      Field 1 Case (Criteria = "Top" ; Number)

      Field 2 Case (Criteria = "Middle" ; Number)

      Field 10 Case (Criteria = "Totally Bottom" ; Number)

       

      From here, I summarise each of these fields so that on my customer layout I can show ten summarised figures for the totals of Top, Middle, Totally Bottom and the other 7 criteria.

       

      However, each time a calc takes place I have the annoying 'Summarsing field sField1 - 120 records to go' and then 'Summarising field sField2 - 134 records to go' etc etc.

       

      I tried ExecuteSQL which worked......eventually.....then killed the entire system.  Won't be doing that again in a hurry.

       

      So, is it just me who see's these summary pop up messages?  Surely the system should be quick enough to do this?  Or can I do something to hide them - if so I have a waiting page ready to go.

       

      Thanks wise ones!

        • 1. Re: Summarising pop up message
          BillisSaved

          Good morning Stu412,

           

          I hope your day is going well. I'd like to clarify a couple things before I offer any suggestions. Would you be able to post the ExecuteSQL () statement you used and tell me what the "Number" field represents? Thanks and have a great day!

           

          God bless,

           

           

          Bill

          • 2. Re: Summarising pop up message
            philmodjunk

            ExecuteSQL shouldn't "Kill your entire system" but it could easily be no better/faster than your summary fields.

             

            What you describe is typical for the design that you have used. It's why summary fields and unstored calculations must be used with care as they only calculate "when needed" and your design requires that a great many calculations all update at the same time--producing that progress bar.

             

            One thing to check before rethinking your entire set up: Are the calculation fields that have the Case Function stored or unstored? I'm wondering if you have a whole bunch of unstored calculations fields in a large number of records that all have to evaluate before the summary fields can even begin to do their evaluations--which would be a major case of "piling on" here.

             

            If they are unstored and you can make them stored values, I'd test that first to see how much improvement that makes. (Calculation fields can be either stored or unstored depending on the storage option specified for them and whether or not they reference data outside the current record (such as in a related record) or a field that cannot be stored/indexed such as a global field, summary field or other unstored calculation field.)

            • 3. Re: Summarising pop up message
              Stu412

              Phil,

               

              The original Case statements are unstored which would lead to them needing to be resolved each time the calc is called.  However, I've left them like this because the values and the selection criteria being referred to in the Case statement can change any time.

               

              If I were to make the Case statements stored, would the change in either the value or criteria fields be sufficient to redo the calcs?

               

              Thanks

              • 4. Re: Summarising pop up message
                philmodjunk

                Stored calculations automatically re-evaluate each time a referenced value is modified. But the possible catch here is that some data references--those that refer to data in a related record or in a global field, force the calculation to always be unstored.

                 

                There are ways, if done with careful attention to all relevant details, to set up a data field with an auto-entered calculation that is then updated via script when any data from a global field or related record is modified. This can sometimes be a way to get much better performance, but only if certain frequent edits don't require updating all records in your table in a great big "batch update".