2 Replies Latest reply on Oct 25, 2013 10:18 AM by Jax

    calculation field does not update for pre-existing entries after formula change

    Jax

      Title

      calculation field does not update for pre-existing entries after formula change

      Post

           Dear FM-pros,

           I had a few entries in the database, then I had to change the formula in Auto-Enter / Calculated value for 2 fields. Now, old entries do not show the correct calculations. It appears they are stuck in time. If I duplicate the entry, the calculation works correctly. If I add a new entry the formula also works.

           Is there a way to force an update of selected / all calculated fields in the old or all entries?

           All the best

        • 1. Re: calculation field does not update for pre-existing entries after formula change
          philmodjunk

               Replace Field contents can be used to update your existing records or you can use the trick that I figured out and describe in this thread: Updating values in auto-enter calc fields without using Replace Field Contents

          • 2. Re: calculation field does not update for pre-existing entries after formula change
            Jax

                 quoted from Updating values in auto-enter calc fields without using Replace Field Contents

                 
                        
            1.                We have several 100 records entered before We realize that this expression should really be ( Numb1 + Numb2 ) / 2
            2.           
            3.                Open Manage | Database | Fields and use the Type drop down to change this field from Number to Calculation and click Change. A warning dialog pops up, click OK to permit the change.
            4.           
            5.                With the field still selected, click the options button to open the specify calculation dialog. The original auto-entered calc expression: Numb1 + Numb2 has been conveniently brought up in the dialog. Edit it to compute the average of these two fields as shown above. Make sure Number is selected as the return type.
            6.           
            7.                Click OK twice to close the Specify Calculation dialog and Manage database. Because We've converted the field to type calculation, the value in this field updates automatically for every record in the table.
            8.           
            9.                Now re-open manage | database | fields and change the field back to type Number. Double click the field to bring up field options, click the calculation check box. We find that ( Numb1 + Numb2 ) / 2 is already entered for us as the auto-enter calculation.
            10.           
            11.                Now all we have to do is close the dialog, clear the "do not replace ..." check box and click OK until we've closed Manage | Database | Fields.
            12.      

                  

                 Hi PhilModJunk,

                 another great tip. I'm becoming a huge fan of yours. smiley

                 In my case, just the change of the field type to Calculation was enough. Everything reset to the correct result where previously ? stared at me confusingly.

                 Thanks a lot and have a great weekend