Parsing Full Name field into First Name and Last Name

Document created by bill.gladstone on Jan 7, 2016
Version 1Show Document
  • View in full screen mode

Parsing Full Name field into First Name and Last Name

 

When importing a data set with a Full Name field most of the names will be two words and easy to parse. Some sub-set will likely be Complex Names with more than two words - with hyphens, titles, suffixes, initials, and/or middle initials. In our experience 10-15% of imported FullNames are complex.

 

This method finds complex names and requires data entry (or confirmation) for the number of words in the last name of each complex name.   (NOTE: in FMP Hyphenated last names are two words.) 

 

In this example we’ll be working with the file “DataBase” with the following Fields:

FullName            To be imported

First            Text (to be Set by script)

Last            Text  (to be Set by script)

Processing            Used to tag and re-find the found set

WordCountName             Calculated

WordCountLast            Entered when needed

WordCountFirst            Calc of Word Count Name - Word Count Last

We will also be working with a layout “List” that includes each of these fields

 

Import the data set that includes a Full Name field (and other fields)

Then run this Parse Name script:

Replace Field Contents [No dialog; DataBase::Processing; “Now”]

Go to Layout [“List”]

Set Error Capture [On]

Perform Find [WordCountName>2 & Processing=”Now”]

If [Get (FoundCount)>0]

# comment:  If found set is null then script will jump to Else

Go to Record/Request/Page [First]

Replace Field Contents [No dialog; DataBase::WordCountLast;2]

Go to Field [DataBase::WordCountLast]

Show Custom Dialog [“Complex Name”;"These Names have more than 2 words. Please check the word count for the last name in each record. The default is 2."]

Pause/Resume Script [Indefinitely]

Else

End If

Set Error Capture [Off]

Perform Find [Processing=”Now”

Go to Record/Request/Page [First]

Loop

            If[DataBase::WordCountName=2]

                        Set Field [DataBase:First;LeftWords(DataBase::FullName;1)]

                        Set Field [DataBase:Last;RightWords(DataBase::FullName;1)]

            Else

                        Set Field [DataBase:First;LeftWords(DataBase::FullName; WordCountFirst)]

                        Set Field [DataBase:Last;RightWords(DataBase::FullName; WordCountLast)]           

            End If

            Go to Record/Request/Page [Next, Exit after last]

End Loop

Replace Field Contents [No dialog; DataBase::Processing; “”]

Attachments

    Outcomes