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 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.

Part I: Introduction to FileMaker Pro

Your First Database

Organizing and Editing Records

Building a New Database

Part II: Layout Basics

Layout Basics

Creating Layouts

Advanced Layouts and Reports

Part III: Multiple Tables and Relationships

Multiple Tables and Relationships

Advanced Relationship Techniques

Part IV: Calculations

Introduction to Calculations

Calculations and Data Types

Advanced Calculations

Extending Calculations

Part V: Scripting

Scripting Basics

Script Steps

Advanced Scripting

Part VI: Security and Integration


Exporting and Importing

Sharing Your Database

Developer Utilities

Part VII: Appendixes

Appendix A. Getting Help

FileMaker Pro 8. The Missing Manual
FileMaker Pro 8: The Missing Manual
ISBN: 0596005792
EAN: 2147483647
Year: 2004
Pages: 176 © 2008-2020.
If you may any questions please contact us: