12 Replies Latest reply on Jun 22, 2009 4:55 PM by BobSchwenkler

    Difference between Text field w/calculation and Calculation fields?

    BobSchwenkler

      Title

      Difference between Text field w/calculation and Calculation fields?

      Post

      I'm wondering what differences there will be between creating a text field and using the auto-enter as calculation option and simply using a calculation field?

       

      The context that this question is being asked within is in situations where I'm pulling text from a field or fields and combining them or adding or deleting punctuation.

        • 1. Re: Difference between Text field w/calculation and Calculation fields?
          philmodjunk
            

          Auto-enter expressions enter data when the record is first created. If you then change the value of a field referenced in the calculation expression, the text field will not change.

           

          With calculation fields that return text, their values will change each time a referenced field's value changes.

          • 2. Re: Difference between Text field w/calculation and Calculation fields?
            BobSchwenkler
              

            But then if I uncheck the "Do not replace existing value" box on the text field?

             

            And the calculation field has a similar option. 

            • 3. Re: Difference between Text field w/calculation and Calculation fields?
              philmodjunk
                
              Bob Schwenkler wrote:

              But then if I uncheck the "Do not replace existing value" box on the text field?

               

              Yep. Try it and see. That option allows the auto-enter to overwrite and existing value but doesn't trigger automatic update when referenced fields are edited. Also, since it references fields at time of record creation, if the values you want are data fields in the same record, they'll be blank at the moment of creation.

               

              And the calculation field has a similar option. 

               

              Not that I can find. You have an option to make it an unstored/unindexed field, but that's different.

               

              • 4. Re: Difference between Text field w/calculation and Calculation fields?
                BobSchwenkler
                  

                I'm a little bit confused. Maybe I'm not understanding you correctly, but I have instances of text fields that are drawing from other text fields using auto-enter calc. (to combine, say, artist name and album name). When I change one of the source fields, the calculated field changes as well.

                 

                And to clarify, the calculation field box I'm talking about is the "Do not store cal. results" in the storage options window. I guess it's not the same, but appears to perform the inverse function of the text field "Do not replace existing..." option. 

                • 5. Re: Difference between Text field w/calculation and Calculation fields?
                  LaRetta_1
                    

                  Phil said, "that option allows the auto-enter to overwrite and existing value but doesn't trigger automatic update when referenced fields are edited."

                   

                  Actually no ... it WILL update its value if a referenced field changes if the referenced field is within the same record OR if the referenced field is in a different table and the current field has a value change which causes its auto-enter calculation to re-evaluate.

                  • 6. Re: Difference between Text field w/calculation and Calculation fields?
                    BobSchwenkler
                      

                    LaRetta wrote:

                    Phil said, "that option allows the auto-enter to overwrite and existing value but doesn't trigger automatic update when referenced fields are edited."

                     

                    Actually no ... it WILL update its value if a referenced field changes if the referenced field is within the same record OR if the referenced field is in a different table and the current field has a value change which causes its auto-enter calculation to re-evaluate.


                     So this being the case, the difference is that a calculation field will update when source fields are changed no matter where the source fields are located, and that a text field with auto enter calculation will only change under the conditions above?

                     


                    • 7. Re: Difference between Text field w/calculation and Calculation fields?
                      comment_1
                        

                      Bob Schwenkler wrote:

                      I'm wondering what differences there will be between creating a text field and using the auto-enter as calculation option and simply using a calculation field?


                      It would take too long to list ALL differences, but the main one is this: if your formula references a related field or a global field or an unstored calculation field, a calculation field will be forced to unstored, and it will update when the referenced fields are modified. A text field will NOT recalculate when a related field is modified (however, it will refresh when a local referenced field is modified, and use the current values for the recalc - assuming the 'Do not replace…' option is unchecked).

                       

                       

                      EDIT:

                      I didn't mean to repeat what was LaRetta already said - I was just too slow to post.

                       


                      • 8. Re: Difference between Text field w/calculation and Calculation fields?
                        BobSchwenkler
                           And going one step further, are there specific instances where I should avoid using auto enter calculation? Situations where it might work fine at the moment but lead to issues farther down the road? Or the same for calculation fields?
                        • 9. Re: Difference between Text field w/calculation and Calculation fields?
                          LaRetta_1
                            

                          Hi Bob,

                           

                          There is very little difference between an Auto-Enter and a calculation; in fact, FM debated whether to include them both.  Use the auto-enter (do not replace) when the value may need to be over-written by a User; use the calculation when not.  Both will update if any referenced field updates (as long as the value changes in the current record).  If the calculation references a related field, it will change to 'unstored'.

                           

                           UPDATE: Yes, auto-enter will NOT update if related field changes even if current record changes - that's why we set it to 'Replace Existing Value' then.  I left that part out.

                          • 10. Re: Difference between Text field w/calculation and Calculation fields?
                            LaRetta_1
                              

                            Hi Michael (Comment) ... you worded it better anyway.  It can be complex to explain all the variations as I found out in trying to word my response.  One additional benefit of a calculation is that, if one decides to change the calculation, it is easier to just change a calculation than to change an auto-enter text field (calculation) because it will not automatically change all existing data. 

                             

                            You would need to re-set the field's ID with itself or force a modification so the new change takes effect.  Be sure to show all records because it'll only apply to the record set you are working on.  The other easy way to force a fresh of auto-enter field is to change the field to type calculation then exit Field Definitions.  This forces recalcultion on ALL records in the table.  Then change it back to auto-enter w/calculation again.  Back up first.  And if you haven't done this before, do NOT make the calculation unstored or you will lose all your data in the field.  But it works on all records in a table and saves looping through all records forcing an update.

                            • 11. Re: Difference between Text field w/calculation and Calculation fields?
                              comment_1
                                

                              LaRetta wrote:

                              It can be complex to explain all the variations


                              Indeed.

                               

                              Here's a related post that could be helpful.