I have records with a "first name" field and a "last name" field. The problem is the "Last name" field has the whole name in it. To make matters worse some of the names are like "John H. Doe"! How can I bring the first name AND the middle initial to the "first name" field? Do I even need a script?

IF every name was the same as your example it would be easy, but you will have some no middle initial, some with 2 first names some with 2 last names, etc.

I think the best thing to do is a calculated for first name and another for last name. For first name the calculation would be something like this:

Case (WordCount(Name) = 2; LeftWords(Name ; 1) ; WordCount(Name) = 3 ; LeftWords(Name ; 2) ; "")

For last name it would be: Case (WordCount(Name) = 2 or WordCount(Name) = 3 ; RightWords(Name; 1); "")

After you complete the calculations change these fields to text.

This will fix most of your data, but you will need to check it and fix any that don't fit the above.

You can either script it, if you're on a networked file with other users currently in it as well, by using

Show All Records

Go to Record/Request/Page [First]

Loop

[color:"white"]__Go to Next Field

[color:"white"]__If [ Get( LastError ) = 301 ]

[color:"white"]____Omit Record

[color:"white"]__Else

[color:"white"]____Set Field [thistable::first name; Left(thistable::last name; Position( thistable::last name; " "; 0, PatternCount(thistable::last name; " " ) ) - 1]

[color:"white"]____Set Field [thistable::last name; RightWords(thistable::last name; 1)]

[color:"white"]____Go to Record/Request/Page [Exit after last, Next]

[color:"white"]__End If

[color:"white"]__Exit Loop If [not Get(FoundCount)]

End Loop

If [Get(FoundCount) <> Get(TotalRecordCount)]

[color:"white"]__Show Omitted {to find any records that were in use at the time and couldn't be changed}

End If

or, put the fields on a layout, enter them, select Records -> Replace Field Contents, and specify the calculations as in the Set Fields above. First Replace on the first name field, then the last name field.

NOTE: Backup your file before performing either of these two modifications.