Newsletter #11
Fixing Names In Your Database

Do you have names and addresses in your file that are all in capital letters? Maybe you imported data that came from some other source. Or maybe you had somebody in your company who just likes to type with the Caps Lock on. It's not really a problem until you have to write a letter and it begins "Dear JOHN."

There is a method some people use to change the way the data looks on their layout, but it is not a permanent fix. I want to describe it to you so you'll know how confusing things can get and you'll know how to fix it.

You can go to Layout mode and click a field that's bothering you. Then go to the Format menu and scroll down to Text. If you click on the drop-down in the lower right corner you can select Title Case and click OK. What that does is make all the data in that field show up in title case, but it really doesn't change the actual data itself. It's sort of like looking through a pair of sunglasses. The world doesn't really turn green, it just looks that way. When it comes to FileMaker, as soon as you switch layouts where the field hasn't been formatted to appear in title case, the data looks the was when it was entered or imported. The same happens when you export the data for use in other applications.

(CAUTION: Before you do this, be aware that people like McKinney will end up Mckinney. Also MaryAnn will be Maryann. So you may have to go back and make some corrections manually.)

Since it is possible to mess up your data in ways you don't intend, I want to show you a safety method.

1) Create a new field called "FirstNameSafety."

2) Add the field to your layout off to one side somewhere. You'll delete the field later on.

3) In Browse mode, click in the field you want to fix. In our case we'll use the FirstNameSafety field.

4) Go to the Records menu and choose Replace Field Contents.

5) Choose the radio button next to "Replace with calculated result." It will automatically open the Specify Calculation dialog.

6) In the upper right, click on the View drop-down and choose Text functions.
Scroll down until you see Proper ( text ). Then double-click it to send it to the calculation box in the lower part of the window. The word "text" should be highlighted ready for you to replace it with a field name.

7) In the upper left, scroll through the list of fields until you see the FirstName field. (Yes, we'll be using the data from the FirstName field to fill in our new FirstNameSafety field which currently has no data in it.)

8) Double-click the FirstName field and it should fill in between the parentheses. It should look like this:

Proper ( FirstName )

9) Click OK.

10) That should take you back to the Replace Field Contents dialog. Click the Replace button in the lower right.

Now you should be able to click through your records an see that the new FirstNameSafety field shows the data the way you would like it with a capital letter at the beginning of each word. You might want to view the names in a list or in table view rather than form view.

The great thing about this method is that you haven't actually touched the data in your FirstName field at all. If you made a big mistake, you can try it again.

After you're sure it worked, click in the FirstName field in one of the records and perform a Replace – only this time send it the contents of the FirstNameSafety field. To do that, when you get to the Calculation dialog, simply scroll to the FirstNameSafety field and double-click it to send it to the Calculation box. You don't need to choose Proper function in this case because the contents of the FirstNameSafety field is already permanently formatted the way you want it.

When you're done, both the FirstName and FirstNameSafety field should have the exact same data (unless you have some kind of formatting applied to the FirstName field on the layout).

Now continue to the LastName and Address fields. You can use the FirstNameSafety field for each step of the process. When you're done, delete the field.


You can get to my other newsletters by clicking here.


Of course, the main purpose of this newsletter is to remind you that I'm here to serve your FileMaker needs - development, support, and training.

If you can think of anyone you might refer me to, I offer a 5% "Thank You!" for the first 2 years of billing on referrals.

J **

< Back to In the News


Email us here.