Jonathan's latest books. > > >
Data Design Pros, LLC
~ Custom Software for Growing Businesses ~

Are you using FileMaker Pro to help run your business? You should be. All other databases are second-class citizens and money pits. (Yikes! That sounds a little harsh.) Read why here.

We make your databases work the way you want them to. That's as easy as it gets. Contact us here.

We are dedicated to serving businesses by designing, constructing, training and servicing tools built with the cross-platform database program FileMaker Pro.

* Save Time
* Increase Profits
* Get Exactly the Software You Need

Click here for information about what can be done with FileMaker

About Us
Learn about our company and how we started.
Products & Services
Find out about our product line.
In the News
Read press releases, newsletters, articles, and reviews of our products. Example below.
Ask the Experts
Get answers to commonly asked questions or send email to our experts.
Sign our guest book to receive product notices and press releases.
FileMaker Advisor Magazine
Jonathan's column
FileMaker Pro
Find out more about the program we use in our application development.
Job Opportunities
Data Design Pros, LLC

What our clients say about our work.
FileMaker Links
Go here for a list of links to other web sites that talk about or use FileMaker Pro etc.
FileMaker 8.5 Book Updates
Additions and corrections.
FileMaker Training

Fixing Names in Your Database

Clean up inconsistent data entry

By Jonathan Stars

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 have 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 this method to you first 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, click OK and go back to Browse mode. What that does is make all the data in that field show up in title case (John instead of JOHN), 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 way it 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 names like McKinney will end up Mckinney. Also MaryAnn will be Maryann. So you'll probably 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 text field called FirstNameSafety.
2) Add the field to your layout off to one side somewhere. You'll delete that field later on.
3) In Browse mode go to the Records menu and choose Show All Records. Then click in the FirstNameSafety field, which currently has no data in it.
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 use 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 and 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 again until you get it right.

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 are 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 FirstNameSafety field.

J **


© 2009 Jonathan Stars


Data Design Pros, LLC is an independent entity and this web site has not been
authorized, sponsored, or otherwise approved by FileMaker, Inc.

Email us here.

web counter

web counter