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 18.104.22.168 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:
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.
As soon as you make this choice, your old friend the Specify Calculation window appears.
The list updates to show only text functions.
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.
FileMaker inserts First Name as the parameter for the Proper function.
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
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
Part V: Scripting
Part VI: Security and Integration
Exporting and Importing
Sharing Your Database
Part VII: Appendixes
Appendix A. Getting Help