Pre-fill new fields with related data, quickly

[David Jondreau]

Sometimes you need to add a field to an existing database that should have values for all the already existing records in the table.

Say you’ve added a new field on an existing database.

It’s not a calc field, but will have an auto-entered calculation field that will always be filled out.

For example, in the table People you add a field called “Full Name”. It’s a combination of First Name and Last Name fields which are in the same table.

= First Name & “ “ & Last Name.

You want users to be able to modify the combination, so it’s an auto-enter field and not an uneditable calculation. Easy enough, except that for existing records, there’s no value in the field for any of the existing records.

You could script adding data, using Replace[], or a Loop and Set Field[], but that can take a long time, especially if the table has a lot of dependencies, there's a large record set, and / or you’re working on a served file. Instead you can leverage the power of the Filemaker’s storage and one little trick.

The trick is if you change a stored calculation field to a non-calculated (Text, Number, Date, etc) field, the values persist in the field.

Make that field a regular calculation field. Save out of Manage Databases. Filemaker then store the data and al your records now have the value you want, but in an un-editable calculation field. Re-open Manage Databases and change the field to Text, check the auto-enter calc box (the calculation should auto-fill) and Save out again.

Now you have a Full Name field where every existing record has the calculated value pre-filled in!

Additionally, this can work with related data.

If you want to pull the Full Name field from your Contacts table into an Invoice table, but have it stored, you would create a text field with an auto-enter calc that looks through a relationship based on INVOICE::fk ContactID <--> CONTACTS::pk ContactID.

To pre-fill all the existing records you again make it a calculation field, but since Filemaker won’t let you store a calculation that references related data, we get help from our friend Evaluate(). Wrap the related table field name inside an Evaluate()

= Evaluate ( “CONTACTS::Full Name”)

You can then store that calculation and save to exit Manage Database. Open Manage Database again, change the field to a text field, remove the Evaluate() wrapper and save to exit again.

Your new auto-enter text field now has related data in all the existing records.

[Lee Smith]

Hi DJ

Thanks for posting this here, I have been meaning to do just this for some time, and kept forgetting it.

Here is one of my posts where I described the process to, hope it helps.

Update Existing Data in Auto Enter

Lee