Updating values in auto-enter calc fields without using Replace Field Contents
Every now and then new users trip over the following scenario:
They add or modify an auto-enter calculation for a field in a table that already contains records and are suprised to discover that the existing records do not automatically use the new expression to calculate updated values in these existing records. Only new records or records where a referenced field is edited apply the new expression to calculate the updated value.
I've often helped people fix this problem by having them use Replace Field Contents with the calculation option and using the new calculation expression to update the existing records.
The following method updates such a field's auto-entered value and is a bit simpler:
For the sake of this example, I'll use a number field with this simple auto-enter calculation:
Numb1 + Numb2
- We have several 100 records entered before We realize that this expression should really be ( Numb1 + Numb2 ) / 2
- 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.
- 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.
- 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.
- 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.
- 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.