Skip to Content
Skip to Table of Contents

← Previous Article Next Article →

ATPM 14.11
November 2008

Columns

Extras

Reviews

Download ATPM 14.11

Choose a format:

E-Mail

Datahand Professional II

R.I.P. Datahand. I hope they manage to sort out manufacturing issues! It’s worrying to not be able to go just out a get another pair, should something happen to these.

—Andy

Apple Keyboard

It sure looks good, and feels like a slightly stretched version of the laptop keyboards.

Which is why I’ll never use it, because my tendonitis flares up like a bonfire when I use a keyboard that’s so flagrantly flat and square.

—Marc Durant

FileMaking: Getting Relational

This is a nice series of articles, well written, clear, and informative. I only wish there were more and that they were more frequently published!

—Chris Knight

Text Parsing With FileMaker

Thank you very much for this tutorial. I am a relatively new FileMaker user and today I taught myself how to create a full name field from fields containing a first name, two fields with middle names, and a last name. I then wanted to turn the process around and parse the full name into its elements . Your instructions were great!! Success! Yippee!!

I was especially pleased at having figured out how to calculate the position of the words in a name that has four parts (i.e. “Sally Ruth Sims Stokes”) and send them to their appropriate fields. Now here’s the catch: I figured out how to do everything except for one thing. I have created a MiddleNameA and MiddleNameB field, as the names table in my database includes individuals other than myself who have two middle names.

If I enter “Sally Ruth Sims Stokes” in the FullName field, my calculations successfully parse the four parts of this name into FirstName, MiddleNameA, MiddleNameB, and LastName. But when I deal with a name combination containing only one middle name, such as “Mary Ellen Jones”, the name parses as “Mary” FirstName “Ellen” MiddleNameA “Ellen” MiddleNameB “Jones” LastName. Could you possibly help me figure out how to get a null value in the MiddleNameB field in such an instance? I would be extremely grateful for your help. Thanks again!

—Sally Stokes

You need to count the words with the built-in FileMaker function WordCount.

Assume that full names can have two, three or four names. For simplicity, I’m not considering prefixes (i.e., “Dr.”) or suffixes (i.e., “Jr.”).

The first name will always be the first word:

FirstName = LeftWords( FullName; 1)

The last name will always be the last word:

LastName = RightWords( FullName; 1 )

The MiddleNameA will be the second word if there are three or more words, and blank otherwise:

MiddleNameA = Case( WordCount( FullName ) >= 3; 
                    MiddleWords( FullName; 2; 1 ); "" )

Finally, MiddleNameB will be the third word if there are four words, and blank otherwise:

MiddleNameB = Case( WordCount( FullName ) = 4; 
                    MiddleWords( FullName; 3; 1 ); "" )

I hope this helps if you need to extend this to more possibilities.

—Charles Ross

Also in This Series

Reader Comments (0)

Add A Comment





 E-mail me new comments on this article