1 2 Previous Next 16 Replies Latest reply on Mar 26, 2010 12:40 PM by johnhorner

    calculation vs. auto-enter calculation best practice

    johnhorner

      Title

      calculation vs. auto-enter calculation best practice

      Post

      i have a general question about when it is better to use a calculation field as opposed to auto entering a calculation.  one specific example is for invoicing which i assume is a very .  a line item on an invoice contains perhaps the quantity, the unit price and a subtotal for that line item which could either be a calculation (subtotal = qty x unit price) or it could be a number field that auto enters the same calculation when either qty or unit price are modified.  a third option would even be to use a script trigger to enter in the calculation whenever one of the other fields was modified (although i think the auto-enter option is preferable in this example as it is embedded in the program an does not require writing a script).  as far as i can tell, it would be advantageous to use the auto-enter method because once the invoice is created, the value in subtotal is unlikely to ever change again and if it is auto-entered, it can be permanently stored and indexed and will speedup finds and sorts.  however, one potential drawback that i can think of, is that i have observed some erratic behavior of the auto-enter option where in some instances (which i can't figure out) it does not always perform the auto-enter calculation as expected when one of the referenced fields is updated.

       

      so i have 2 questions:

       

      1.  which method would be preferable for this particular situation?

       

      and

       

      2.  is there a general rule of thumb or set of criteria to determine when you would use a calculation field and when you would use auto-enter

       

      any thoughts/recommendations/information would be much appreciated.

       

      thanks,

       

      john

        • 1. Re: calculation vs. auto-enter calculation best practice
          TSGal

          johnhorner:

           

          Thank you for your post.

           

          A calculation field would update any time one of the fields/variables is changed.  An auto-enter calculation would occur the first time a record is entered.  It may not necessarily change if one of the fields/variables is later changed.  You may enter a product ID which looks up a price.  You enter a quantity and the calculation price * quantity would occur.  If you the order changes and the customer wants a smaller quantity, you can change it and the calculation field would update immediately.

           

          I think it would be better suited to have a calculation field in this instance.

           

          I hope this information helps.  Let me know if you need clarification for any of the steps above.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: calculation vs. auto-enter calculation best practice
            raybaudi
               ... and a calculation field would update ALL records of the DB, while an auto-enter calculation may ( or may not depending by a little flag ) update only the CURRENT record.
            • 3. Re: calculation vs. auto-enter calculation best practice
              johnhorner
                

              hi tsgal,

               

              thanks for the reply.  i do have a follow up question.  i have a database that has been evolving for several years and is currently set up with the "line item subtotal" as a calculation field, but i have noticed recently that it takes a long time when i use certain layouts that display a large number of records (appx 1500 records each with about 10 line items) that it takes a long time (10-20 seconds) for it to calculate or sort the records before it will display them in portal.  so it was in response to this delay that i figured it might run faster if no calculations were needed.  i made a test subtotal field that used auto-enter for the calculation and it seemed that as long as i had "don't replace field contents" unchecked it was always updating the subtotal whenever i changed either the quantity or the unit price.  i also discovered that if i uncheck "allow override" in the validation window, it also prevents users from accidentally entering a value that is not a result of the calculation (similar to the way a calculation field operates).  however, i have noticed other times (with more complicated relationships) that auto-enter does not neccessarily work every time (as you mentioned).  is there any literature specifying the conditions under which it re-calculates and when it doesn't?

               

              asuming that the auto-enter calculation did work reliably every time the item qty or unit price was modified, are there other reasons why you would recommend a calculation field over an auto-enter calculation?  does it make for a smaller file for example?

               

              thanks again!

               

              john h.

              • 4. Re: calculation vs. auto-enter calculation best practice
                johnhorner
                  

                hi daniele,

                 

                thanks...that is an excellent point.  while that would not be an obstacle in terms of the line item subtotal (because i would not want that to recalculate if the unit price went up, for example, on an item at later date because at that point the invoice on which it appears has already been sent and, hopefully, paid for so it should not change).  but it is a good reason to use a calculation field instead of an auto enter calculation for some other fields i was thinking of converting.

                 

                i appreciate the input... thanks,

                 

                john h.

                • 5. Re: calculation vs. auto-enter calculation best practice
                  comment_1
                    

                  Auto-enter calculations do work reliably. However, they will NOT update automatically when a related field is modified. This behavior is intended.

                   

                  A calculation field can be stored (this is the default). Storing the result increases file size and speeds up the rendering of layouts. An unstored calculation is re-evaluated every time the window is refreshed.

                   

                  The common method for line items is to lookup the price, and use a stored calculation field to calculate the line subtotal (e.g. price * quantity).

                   

                  IMHO, a good thumb rule for beginners is to use a stored calculation field for calculations, unless there is a good reason not to.

                  • 6. Re: calculation vs. auto-enter calculation best practice
                    johnhorner
                      

                    hi comment,

                     

                    thank you for your reply.  it seems that all three people who have posted a reply agree that a calculation field is preferable to using an auto enter calculation for this specific example which is compelling reason alone to use it.  however, no one has offered a convincing reason yet whythis is so.  the auto enter calcualtion field will work exactly the same way.  if either the item qty or the unit price is modified, the subtotal will auto update (i have tried it using the settings outlned in one of my previous replys, and you can prevent it from being modified).  so to someone inputting data the operation is identical to that of a calculation field.  they both update upon modification of either item qty or unit price, they can both be indexed, and they can be set so that they both prevent modification by direct user input.  given all that, no one has offered an explanation yet why the calculation field is prefereable for this specific purpose (it is clear why it would be preferable or even necessary in other situations).  it would seem (except for the fact that everybody says to use a calculation field) that the auto enter calculation would be preferable because it does not require the continual additional computational overhead of a calculation field.  

                     

                    any additional insight you could offer as to why the calculaiton field is preferable would be appreciated.

                     

                    thanks,

                     

                    john h.

                    • 7. Re: calculation vs. auto-enter calculation best practice
                      ninja
                        

                      johnhorner,

                       

                      A fourth opinion...and given only 'cause you asked for more, the folks above are both correct and compelling.

                       

                      There are at least four ways to put your calculated value in the field

                      1. autoenter calculation

                      2. calculation field unstored, recalculate when needed

                      3. calculation field indexed

                      4. number field with a manually launched script to setfield based on a calculation.

                       

                      Each has strengths and weaknesses and the choice would be made depending on the situation.  I can see two ways I personally would consider in the situation you describe.

                       

                      1. Calculation field unstored (assuming less than 30K-50K records).  This way when an order is modified because the customer cuts the order in half due to the economy, the line items automatically correct themselves and save me from printing an incorrect invoice and embarassing myself in front of the customer.

                      If the record count is much higher, you might want to reconsider this based on data handling speed.

                       

                      2. Script trigger to recalculate single record only.  This way if I increase my prices, it won't change all of my old invoices that I might want to use for reference later on when doing my taxes or corporate statements at year end.  This again depends on record count and company size/type.

                       

                      looking at today's use only, I'd go with a calculation field in a heartbeat.  Looking at the bigger/longer term picture I might reconsider...but I'd reconsider based on my own company's needs and how I might want to use the data later...it becomes a very personal and subjective call at that point which no one can make but me.

                      Calculation field flat-out works...other nuances may be available to you by the other methods.

                       

                      Enjoy the day!

                      • 8. Re: calculation vs. auto-enter calculation best practice
                        comment_1
                          

                        IIt's true that a STORED calculation field behaves very much like a field with auto-entered calculation, with the option to replace existing data turned on. As long as your formula does not reference any related or global fields, probably the only advantage of a calculation field is that the formula is more accessible for viewing and editing.

                        I believe it is good practice, though, to make the field for what it really is - a calculation. For example, users can freely type into fields with auto-enter calculations, only to see their effort disappear as soon as they exit the field.

                        It is also important to remember that we are comparing a specific configuration of a calculation field (stored) against a specific configuration of an auto-entered field (replaces existing data, does NOT reference a related or a global field, or itself). In other circumstances, there will be HUGE differences between the two types.


                        "auto enter calculation would be preferable because it does not require the continual additional computational overhead of a calculation field. " 

                        There is no such advantage, because if the calculation is stored, there is no "continual additional computational overhead". The calculation evaluates only when one of the referenced fields is modified. The result is then stored and the stored value is used until the next evaluation.




                        • 9. Re: calculation vs. auto-enter calculation best practice
                          johnhorner
                            

                          hi ninja,

                           

                          thanks... i am glad you decided to contribute.  it is very helpful for me to hear everyone's solutions and opinions.  as you can probably tell, i am struggling to really understand not only this particular situation, but also the reasoning behind it so that i can figure out the best method to use for other situations going forward.  i also realize that from the beginning i did not give quite enough inormation about the structure and operation of my database so that people could fully understand the nature of my confusion regarding the 4 methods as you describe.  i have a table for invoices keyed off of invoice#.  when i enter in a client code it performs an auto lookup for the client name and address for the invocie from an address table keyed off of the client code.  there is a portal in invoices to a table called line items related by invocie#.  when i enter an item code into the appropriate field in the portal for line items, it performs an auto lookup for the item price from a price list table related by item code.  it also automatically enters in a qty of 1.  at the moment, i have 2 subtotal fields, one is a calculation field and one is a number field with auto-enter (replace contents, do not allow user override.  both fields display the same correct subtotal.  if i go to the price list and change the price of the item just entered, there is no change to either the item price in the line item, or either of the subtotals.  it only updates if i change the item code in line items and then it looks up a different item's price.  however, if the order is cut in half and i go to item qty and enter a new value, both subtotal fields update for that line item and show the new, correct subtotal.  since the auto enter subtotal field is set so that it does not allow "user override" during data entry, it is not possible to accidentaly enter in a number other than the correct subtotal into the auto enter field.  similarly, you cannot accidentally enter in a value into the calculated subtotal.  you receive an error message in both situations.

                           

                          i am assuming that your invoicing database is structured differently because i can't imagine a situation under which the line items would not automatically correct themselves resulting in an embarassing mistake. under what circumstances would this occur?

                           

                          i think i may be missing a basic concept here which makes me worry and leads me to another perhaps more involved question:

                           

                          am i using a less than ideal structure for my database (i.e. is this not how it would typically be set up)?

                           

                          sorry to be a nuisance... i really appreciate all of the input and feedback!

                           

                          john h.

                          • 10. Re: calculation vs. auto-enter calculation best practice
                            comment_1
                               Allowing user to override or not is a validation feature. I don't see how it's relevant to the present issue.
                            • 11. Re: calculation vs. auto-enter calculation best practice
                              johnhorner
                                

                              hi comment,

                               

                              thanks for your ongoing efforts to explain the reasoning behind the preference.  and please accept my apologies for being so slow to catch on.  i think that your most recent explanations make complete sense to me now.  and the "good practice" argument carries a lot of weight because even if it does not make a whole lot of functional difference in this one, very particular instance, it makes more programming "sense" to make the field what it really is (and of course it is what anyone else looking at the database would expect to find).

                               

                              thanks for hanging in there, that really helps me out a lot going forward.

                               

                              john h.

                              • 12. Re: calculation vs. auto-enter calculation best practice
                                ninja
                                  

                                Howdy johnhorner,

                                 

                                You just typed a magic word that pulls the four+ choices down to one.

                                 

                                If indeed you are using a "lookup" to get customer info and a "lookup" to get price info, then you want to use a calculation field...period, end of story.  comment is correct, make the field what it is.

                                 

                                In my Dbases, I do not use "lookup" unless I have to...there are just too many records for this to be done quickly enough.  I have Table occurrences that refresh the record that I'm on and also let other builders view how the Dbase works more easily.  All this aside, and probably off topic anyway, I would recommend that you follow all of the good advice and the correct reasoning behind it that the other folks on the chain have left.  My post assumed that your invoice line items were portaled in from a related table.  If you are using a lookup instead, then you aren't faced with real-time referenced price changes.

                                 

                                comment, nice work.

                                • 13. Re: calculation vs. auto-enter calculation best practice
                                  johnhorner
                                    

                                  thanks ninja,

                                   

                                  i will definitely follow the advice from you and others and use the calcualtion field!  and thanks for the example of a situation where the auto enter method would fail (namely, real-time pricing), that never occurred to me.  i am in the process of taking a database that has been evolving for perhaps 10 years and does not conform to any sort of standard whatsoever and revising it to conform with the current best practices (to the extent that i am able), so all of this information will be quite useful!

                                   

                                  all the best,

                                   

                                  john h.

                                  • 14. Re: calculation vs. auto-enter calculation best practice
                                    Enigma
                                       There is another benefit to a calculation over an auto-enter field ... if you change the calculation, all records will recalculate when you exit field definitions.  Not so with auto-enter and you would have to loop through all the records forcing the field data to change according to the calculation.
                                    1 2 Previous Next