A READER ASKS: How can I get this FileMaker field filled in?

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. For the best experience, please visit the journal directly by clicking (here). 

It’s the first time I'vd used FileMaker Pro and I have a question. I have a databank with 250 records and 40 fields. If I add afterwards a new field (a calculation of some existent fields), how can I get this field filled in for all the already existent records?

Can you help me?

A student from Belgium

No problem and welcome to the FileMaker party!

The quick answer is the Replace Field Contents command from under the Records menu. I don't know how many times over the years I've show this to users and received feedback of pure astonmishment.

The Replace command (under the Records menu in browse mode) allows you to batch update a found set of records. Generally, a user will user either batch update the records with a static text string or a calculated value. There is no undo for the replace command.

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