1 2 Previous Next 18 Replies Latest reply on Nov 5, 2015 9:34 AM by HB

    Summary field vs Calculation field

    HB

      I have looked but can't seem to find a good comparison analysis between using a max Summary field vs a max Calculation field.

       

      If I want to occasionally run a script that finds the maximum number used in a number field of a table. I currently use a script that finds all records, checks the value of the summary field, but I am wondering if it would be wiser to use a calculation field showing the maximum number of that field???? I have a large amount of data - things are pretty slow as is.

       

      Thanks.

        • 1. Re: Summary field vs Calculation field
          jbante

          Have you run any tests of your own comparing how much time it takes to do each?

          • 2. Re: Summary field vs Calculation field
            taylorsharpe

            Summary Fields and calculation fields are "Live" fields continually being updated.  Normally this is great because when you change a number in a record or the found set, these fields update automatically.  Same if someone else updates a record in the same table while you are looking at it.  Pretty slick.  That is also an achilles heel for large data sets or ones that are over limited bandwidth such as the internet.  One solution is to have a script create a global variable ($$) for when you go to that layout or do a find and it calculates the result once.  The disadvantage is that it will not update unless you do something that script triggers it update or have a refresh button.  I have had reports with a LOT of summary fields that I made global variables and I can then scroll through the layout quickly compared to when it has summary or unstored calculation fields.  Actually, I often create virtual reports from global variables to make reports go fast where data does not have to be edited or manipulated.  But that is a whole other issue. 

            • 3. Re: Summary field vs Calculation field
              keywords

              As I understand it, if you want to use a Max() or similar function in a calc you would have to do this through a cartesian relationship, otherwise it will only look at the current record. A Summary, on the other hand, will confine itself to the current found set.

              However, some very useful stuff in the post by Taylor Sharpe.

              • 4. Re: Summary field vs Calculation field
                siplus

                a calculation field will always give you the same result.

                a summary field might give different results, depending on the sort order and layout part where it sits.

                • 5. Re: Summary field vs Calculation field
                  keywords

                  And the found set.

                  • 6. Re: Summary field vs Calculation field
                    LucThomaere

                    If you create an internal relationship to match all records of the table, you can easily use the Max-function to calculate the largest number used in the whole table, no matter which records are selected...

                    I have no idea if that will be a lot faster than a Summary-field, but is is easier and less complex

                    • 7. Re: Summary field vs Calculation field
                      Mike_Mitchell

                      No, that's mistaken. The type of relationship's not relevant.

                       

                      "Max(Payments::PaymentDate) returns the most recent date a payment was made on an account."

                       

                      Max

                      • 8. Re: Summary field vs Calculation field
                        keywords

                        Sorry Mike, but I disagree. Key words in FM description of Max are "any related field". See attached demo.

                        • 9. Re: Summary field vs Calculation field
                          Mike_Mitchell

                          My objection was to the "have to use Cartesian join" part. Any relationship will work. Any of the aggregate functions will properly evaluate the related set, regardless of the type of relationship used. See (revised) demo file.

                          • 10. Re: Summary field vs Calculation field
                            siplus

                            The old adage saying "pay attention to what you wish for, you might get it sooner or later".

                             

                            One important responsibility in designing FM solutions is to anticipate problems which appear after a long period, as the client gathers data. Max() on 1000 records, no matter how you calc it, is different from Max() on 1000000 records.

                             

                            Such examine-n-records-to-give-an-answer results must be clearly defined with the client: are they functional or merely informational ? In the latter case, press a button, do an ExecuteSQL, get your answer and basta.

                             

                            With one client I went from "how much I invoiced" once per month via pdf sent by email, all the way to slot-machine like real time update every x minutes, useless slow down of the whole solution for the 40+ people actually doing the work instead of salivating on $$pron.

                             

                            So... what do you need that max for ?

                            • 11. Re: Summary field vs Calculation field
                              keywords

                              Fair enough. I find I rarely disagree with you, Mike.

                              • 12. Re: Summary field vs Calculation field
                                Mike_Mitchell

                                Excellent advice. You can wind up hating yourself - or the developer - if the solution slows to a crawl when the data load starts to climb.

                                 

                                Consider archiving, batch jobs, summarizing tables, and other solutions instead of real-time aggregation. In many cases, these are superior solutions in the long run.

                                • 13. Re: Summary field vs Calculation field
                                  Mike_Mitchell

                                  Nor I you.  

                                  • 14. Re: Summary field vs Calculation field
                                    HB

                                    In this particular case I am actually updating a new product record with a UPC code. I find the highest UPC existing then add one number. The field is a number field, and it has various lengths. Most UPC's come from suppliers, however Isome do not in which case I have a many to be something like 9999999000001 etc. I want the next one to be 9999999000002 etc. I did not design the database, but can modify things. I currently use a script that finds all records, checks the value of the MaxUPC summary field, add 1 to it and then update the field value. There is around 19,000 product records x 308 fields, the data file is around 60,000kb.

                                     

                                    Thanks

                                    1 2 Previous Next