Changing to Last name first

[dmui28]

Good morning all,

I am new at this, and need some help converting names.

I would like to convert "First Name Last Name" to

Last Name, First Name.

Thank you in advance.

Filemaker Noob

[TheTominator]

For a field called FullName a quick and dirty way to do this is

RightWords(FullName; 1) & ", " & LeftWords(FullName; 1)

If your FullName fields contains a more complex data than simply "John Smith", it fails. "Mr. John Pigglesworth-Smith, Sr." sends it home in tears.

A full-featured firstname, lastname reversing tool that considers the possibility of suffixes and compound lastnames requires more elaborate programming.

Edited January 10, 2010 by Guest

[dmui28]

Thank you!!!

So far I do not see any suffixes in my list of names...

[dmui28]

Problem with names with a hypen in them, any suggestion to fix this?

[Lee Smith]

You should have separate fields for First Name, Last Name, Middle Name, etc.

This topic has come up in the past several times.Take a look at this Thread for some information on how this can be done. Link

Lee

[comment]

Assuming the only space in the field is the one separating first and last name, you could use =

GetValue ( Substitute ( FullName ; " " ; ¶ ) ; 1 )

for FirstName, and =

GetValue ( Substitute ( FullName ; " " ; ¶ ) ; 2 )

for LastName.

[ferdly]

I have a Two-Pronged response.

1. Don't worry about a calculation to transpose a "First Last" field to a "Last, First" expression. Instead create NameFirst and NameLast fields (and create NameSalutation, NameMiddle, NameFirstPreferred ['Bob' for 'Robert' etc], and a NameSuffix fields while you are at it). Use whatever logic (some already posted and more below) to Populate these fields with their respective correct values and THEN (when you have fully broken up the "First Last" data as best you can and made a backup just incase) change the source field (let's say it was "Name") to a Calculation field whose calculation is Trim(NameFirst & " " Trim(NameLast)) ==> "Robert Smith". Then you can also make a NameLF calculation Trim(Trim(NameLast) & ", " & NameFirst) ==> "Smith, Robert", and then you can create a NameFormal calculation Trim(Trim(Trim(Trim(NameSaluation & " " & NameFirst)& " " & NameMiddle) & " " & NameLast) & " " & NameSuffix) "Mr. Robert Peirpont Smith Sr." and a CasulaGreeting "Dear " & Case(IsEmpty(NameFirstPreferred); NameFirst; NameFirstPreferred) & "," ==> "Dear Bob," and so on. (I am sure these are imperfect, but the liberal use of Trim in these calculations and/or Trim with data entry trigger scripts can avoid headaches later.)

2. Here are some tips on Logic for doing the parsing (with field name for "First Last" being "Name".

a. create a (or use an existing) temporary field and use the Replace() function to put in WordCount(Name). Then find all the records where the value is equal to 2 and use the logic already supplied, but use Replace () to make NameFirst LeftWord(Name) and NameLast RightWord(Name), now for all these records replace your temporary value with "DONE".

b. Find the Non-"Done" records (of course, when the number is zero or sufficiently small to indicate manual human parsing, just stop). Then on only those records replace the temporary value with Case(WordCount(Name) <> 3; 0; LeftWord(Name) = "Mr."; 1; LeftWord(Name) = "Ms."; 1; LeftWord(Name) = "Mr"; 1; LeftWord(Name) = "Ms"; 1; 0)/*forgot wether LeftWord removes the period, I think it does, but this calcualtion will cover both possibilities*/. Then find all the 1's. Apply Leftword to Salutation, MiddleWord to First and RightWord to Last. Mark these as "DONE".

c. Find the Non-"Done" records again and replace with PatternCount(Trim(Name); " "), find all the 1's (these will be the hyphenated last name people and/or the hyphenated first name people) for these you can replace NameFirst with Trim(Left(Trim(Name); Position(Trim(Name); " "; 1; 1)) and Replace the NameLast field with Trim(Right(Trim(Name); Length(Trim(Name)) - Position(Trim(Name); " "; 1; 1)). Mark these as "Done". From here I hope that you are down to a manual parsing manageable number since it gets really hinky here. "Mr. James Prescott Feniwick III" or "Yvonne de Carlo" ('de' is not the middle name, but part of the last name) also "Jo Beth Williams" ('Jo Beth' is considered the first name, although admittedly the NameFirstPreferred variety... nice seguay to)

d. Return to ALL of the Done records and Replace the NamePreferredFirst value with NameFirst, then find all the "Bob"s and replace NameFirst with "Robert" and so on... try to catch the "Big Ones" here (Bill, William, Kathy, Katherine and so on). Note: only Change NameFirst from "Bob" to "Robert" never change NameFirstPreferred "Robert" to "Bob" unless you are certain that it is indeed preferred. [Even "Bob" to "Robert" can get you in trouble as my name is "Brad" and neither "Bradley" nor "Bradford" and this used to upset me as a child when adults would insist it was one or the other, hence my nickname "Ferdly" -)]

e. As per step #d, be sure that all of your NameCalculations will elegantly accomodate where the NamePreferredFirst value is Empty or Identical to NameFirst and to handle these two cases identically. The only reason for the extra field is where the two values are different.

I hope that helps.

Ferdly