Parsing Full Name field into First Name and Last Name

Version 1

    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; “”]