Better way to update fields once calc revised?

[surfer]

Hello-

Whenever I add or modify a calculated field, in order for it to take effect, I have to re-enter the fields that it depended on. Is there a way to make the fields update themselves automatically?

For example, to find out the age of a client, I enter their date of birth into "DOB", then via an auto-entered calculation "Age" will be populated with the correct age depending on the date in "DOB".

But, for records that had the DOB entered BEFORE I added the calculation to "Age", the "Age" will not update. In order to get it to show the calculated age, I have to re-enter the date of birth into "DOB"

This can be burdensome with lots of records, is there a setting, etc. that would take into account the previously entered date in "DOB" and calculate the age into "Age" without me re-entering the date into DOB?

Thanks. This forum is great!

[-Queue-]

Have you deselected the 'Do not replace existing value for field' option?

[surfer]

Yes- I have deselected the Option: 'Do not replace existing value for field'

This is a recurring problem that I am having with previously entered records.

For example: I had 2 fields: "Age" and "DOB". I entered my records initially by entering the age of the client manually into the "Age" field. I later modified the "Age" field into a calculated result using the date that was entered from the "DOB" field. It works for new records, however, all the previously entered records do not update and replace the manually entered data.

This problem keeps coming up as I continue to do modifications to the database with new formulas, lookups, etc.

Any ideas???

[-Queue-]

Are you using Get functions in your calculations? They must be unstored in order to update automatically.

You may need to run a manual Replace once a calculation is changed, since it is an auto-enter calculation and not a true calculation field in the <7 sense of the word.

[ESpringer]

I'm suspecting that surfer needs a plain-old calc field type, not an auto-enter calc. That way, the age will work directly off the current actual value in the DOB field.

surfer, was there a reason you want the value stored "hard" rather than calculated?

[dbruggmann]

If you calculate the age by the auto-entering option these values will be soon out of date, because the field doesn't update itself anymore. If you need always the correct age of the person, you should follow ESpringers advice and define the age field as a unstored calculation. If "Age" is furthermore used for a relationship, you have to incorporate a mechanism that updates the field (via script or re-lookup) every time you need it.

Otherwise I would go with a one time script using the Replace Script Step, as Queue suggested. Choose the option "by calculation", enter your age formula and select "without dialog". Don't forget to show all records and to be on a layout with the age field present. The Replace command only works with the found set and the present fields on the particular layout it is run on. You can use similar scripts for future changes to your database. As you will usually run these scripts just once, it is the easiest way, I think. But backup your data every time before

[surfer]

ESpringer-

Thank you for the advice. I have currently defined the "Age" field with the following auto-enter calc:

Auto-enter / calculated value / deselected: Do not replace existing value for field (if any)

With the following calculation (kudos to the Forum):

Year ( Get ( CurrentDate ) ) - Year ( DOB ) - ( Get ( CurrentDate ) < Date ( Month ( DOB ) ; Day ( DOB ); Year ( Get ( CurrentDate ) ) ) )

What did you mean by replacing it w/ a plain-old calc?

I would like to consistently update the age of the client.

I appreciate the help...

[-Queue-]

Change the field type to calculation, not text or number with an auto-enter calculation. Make it unstored in the storage options, and you'll have an automatically updating calc.

Detlev, I believe that Replace no longer requires the field to be present on any layout--one of the many v.7 changes.