Replace Field Contents
Compiled by Stuart Gripman
Here's how to change the values of one field in a bunch of records simultaneously.
If you use a database long enough, you're bound to encounter a situation where you need to change the values of one field in a bunch of records at the same time. If you've ever gone from record to record making the same change over and over again, surely you've thought there must be a better way. There is.
The Replace command is a powerful tool for updating many records at once. Effectively, Replace deletes the contents of the currently active field and inserts the data of your choosing for every record in the found set. It's important to exercise caution when performing a Replace. It always operates on the entire found set of records -- and you can't reverse it by using "Undo." I routinely save a back-up copy of my database before a Replace. It's the only way to go back if you make a mistake.
Replace with current contents
Replace has three methods of operation. The first Replaces the field data in all records to match the current record's value. Click into a field and choose "Replace Field Contents…" from the Records menu. If you click on "Replace," all the records in the found set will now contain the same value as the active record.
Replace with serial numbers
This option lets you apply serial numbers to the Replace field. The initial and increment values must be integers. If the field you're replacing in is defined as a serial number field, the "Update serial number in entry options" checkbox will be available. Checking it will update the field's serial number settings to ensure that records you create after the Replace action will have appropriate serial numbers. One word of caution: No matter which record you invoke the Replace from, the initial value will be applied to the first record in the found set.
Replace with calculated result
Here lies the true power of Replace. Choosing this option opens a standard calculation dialog box. If you can calculate it, you can Replace with it. One very common application is to append information to an existing field. Say you have a notes field and you want to add "Sent Summer 2006 Card" to the notes of each record without overwriting the existing notes. The calculation below would do the job:
notesField & "¶Sent Summer 2006 Card"
This calculation simply retains the existing contents of the notes field and adds the text on a new line at the bottom.
It's not all roses and sunshine with the Replace command. It bears repeating that you can't undo a Replace, so be sure to save a backup before every significant Replace.
You can perform a Replace in a script, but be aware that the field being replaced must be available on the current layout at the time the script is run. If the target field isn't available, the Replace won't take place and FileMaker won't generate an error message. Write your scripts to go to an appropriate layout before invoking a replace.
Finally, you can perform a Replace on a networked database, but it can be slow. FileMaker Pro has to move a lot of data across the network for each record involved in the Replace, so even a few large records can take a long time. More importantly, when multiple users are connected to a database, Replace can't update records that are being edited by other users. Under these circumstances, you will get an incomplete Replace that skips those records locked up by other users. Although FileMaker Pro will alert you to such an error, it will not tell you exactly which records could not be updated. Generally, if you need the Replace effect in a multi-user database, a looping script is a better choice.
Thanks David. I failed to mention that I do not want to replace the field data in every record. Just in the records where the field is now blank. Any suggestions?
Perform a FInd to find just those records where the field is blank, then follow David's instructions.
To find records with a blank field:
Enter find mode
Enter just an equals sign in the blank field
Perform the find