Using The FileMaker Replace Command

From Dwayne Wright PMP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit or other formatting of the posting may fail. For the best experience, please visit the journal directly by clicking (here)

WORD OF CAUTION: Due to the "no undo" aspect of this feature, always use it with caution. In fact, I normally remove this feature as a menu option for my users. This can be done by using Custom Menus in FileMaker Advanced.

After performing a find operation, there are times you will want to update the data in a field for all the records in the found set. One quick and easy way to do this is to use the Replace command. The Replace command, from under the Records menu, can be used to replace the contents of a field in all records of the current found set. You can exchange the fields contents with a literal data string, an incremental serial number string or a calculated result.

There is also an associated Replace Contents script step. This script step allows you change the information in a particular field for all the records in the found set. You can change it to the same information in the record you are browsing, change them all with a sequential serial number or change them all via a calculated result.

The replace with serial numbers options does just about everything as advertised. It can update the first record with an initial value and then increment the serial value for each following record. This is quite handy when you have imported data into a file. You may or may not want to sort your found set of records before you re-serialize the values in them. Serialization can include alpha characters but they do not increment. That is to say a serial number value of A99 will become A101 on the next created record and not B01.

The most powerful of option for replacing data is via the calculated result. Using a calculation result allows you to evaluate a set of conditions and return a dynamically created text, number, time or date result. In the replace context, a calculated value can be placed within the entire set of found set records.

You can even use the field you are replacing on itself. For example let’s say the boss gives everyone an extra day off in the next year instead of a Christmas bonus. You can click into the days off field, if it’s just a straight number field. Use the calculated result in the replace command to set the field equal to itself plus one. The steps would be ...

1) Make sure you have the correct found set that you will be replacing. Remember there is no undo for the replace command. You might even consider making a backup of your database files if you are going to be doing a lot of replacing.

2) Next, make sure you are in browse mode and click into the Days Off field. Choose the Replace command from under the Records menu.

3) Choose the calculated result and place the following calculation into the field ( Days Off + 1 ) and then execute the replace.

So if one user gets 10 days off a year, the value will increment to 11. If the employees gets 15 days off a year, it will increment to 16. If an employees doesn’t get any days off a year, it will increment to 1.

Now you might want to say that employees that do not get days off (like part time employees), you don’t want it to increment to 1. Well, you can make sure your find has those users in the omitted set before you do the search ... or ... you can branch your calculation to pass over users with zero days off.

=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.