Replacing Data Using a Calculation

You can use calculations productively with the Records Replace Field Contents command. This command tells FileMaker to visit every record in the found set, replacing the contents of one field with something new. When you first saw this command in Chapter 2, you entered a single value in every record. Sometimes, though, its more useful to have FileMaker use a calculation to figure out what should go in each record.

Tip: Replace Field Contents is a huge time saver, but since it works on a found set of records, you can't undo it. Improperly used (bad calculation or the wrong found set), it can be destructive. See Section 19.1.1.5 to learn how the Data Viewer in FileMaker Advanced helps you preview the results of a calculation before you make that one-way trip through the Replace Field Contents dialog box.

Imagine you attended the International Private Investigators conference in South Dakota, where you picked up 73 business cards you'd like to add to your database. Rather than type them yourself, you told your 13-year-old nephew you'd give him a gumball for each card he typed in. After the cards are tossed and the gum given, you discover he has an aversion to the Shift keynone of the names are capitalized.

You could go through all the records one by one, fixing the capitalization and regretting the cost of the gumballs. But if you use the Replace Field Contents command, you can do all your records with just one command, using a calculation. You use the Proper function, which capitalizes the first letter of each word it encounters. Here's how:

1. From Browse mode, click the First Name field.

   The Replace Field Contents command operates on the field you're in when you run it. So click to start in the correct field first.

2. Choose Records Replace Field Contents. In the Replace Field Contents window, select the "Replace with calculated result radio button.

   As soon as you make this choice, your old friend the Specify Calculation window appears.

3. Choose "Text functions" from the View pop-up menu (above the function list).

   The list updates to show only text functions.

4. Double-click "Proper ( text )" in the function list.

   FileMaker inserts the Proper function in the calculation box. Since it has only one parameter, it's already selected. You just have to tell it what text to perform its magic on.

5. Double click the First Name field in the field list.

   FileMaker inserts First Name as the parameter for the Proper function.

6. Click OK to close the Specify Calculation dialog box. Then, in the Replace Field Contents window, click Replace.

   Noticeand bewarethat the Cancel button in this dialog box is automatically highlighted. If you hit Enter too quickly, or accidentally click Cancel, you have to start all over, because the dialog box doesn't remember your calculation. It's worth taking an extra second to make sure you're clicking the appropriate button.

If you have a lot of records, you see a progress dialog box. Normally, though, the replacement happens quickly enough that you don't even notice.

Tip: FileMaker has two other functions for changing case: Upper and Lower. It probably goes without saying that Upper converts all the text to uppercase, and Lower converts it to lowercase.