Parsing out names, once and for all

You know the problem: You've got a list of names you want to sort alphabetically by last name but the first and last names aren't in separate fields, it's all just one big string. No big deal, right? You just look for the space:

LastName := @Right(ContactName; " ");

Piece of cake. Oh, wait. What about double last names? You know, like St. John or Van Dyke? Just taking things right of the space isn't going to cut it there. You might take everything right of the first space, but what if there's a middle name?

Or, triple last names? Like, van der Wald. It just keeps getting worse.

While your head is spinning about that, consider that some of these people have titles they like to see in their names as suffixes. Or, prefixes, even. Some have more than one. So, you've got this name you want to arrange in a last-name-first format:

Dr. Horace Allen van der Wald, Esq., MD, PhD

Something like that.

And, just to make it interesting, let's say that intermixed in your list of contact names are Contact documents you used for related but different purposes, like keeping track of numbers for the Help Desk or Technical Support or the Marketing Department. Those names, of course you wouldn't want rearranged. You don't want it to say "Desk, Help" you want it to still say "Help Desk." Right next to "van der Wald, Dr. Horace Allen, Esq., MD, PhD."

That's what you want. So, the question is, how on earth can you possibly write a view column formula to do it?

You know how on the old show they used to say, "This is a job for Superman!"? Around here the sentence was, "This is a job for BigBill!" We had a need to do exactly what I've described above, for our own client contact database.

This was definitely a job for Bill. This was the kind of thing that made the rest of us grin with pleasure at the thought of not having to figure out and made him grin with pleasure at the challenge.

It sounds impossible to do. Hell, it is impossible to do by mere mortals like you and me. But, he worked it out. You can get your own copy of the code here. Feel free to use it as you see fit, but please leave the copyright statement in place.

At the top, just under the copyright is a place where it says YourFieldNameHere. Replace that with...you guessed it...the name of the field on your form which contains the name. If you actually have a field called Name, you'll need to do some find-and-replacing.

Further down the page, in all caps, are the words it's looking for in terms of multiple last names, prefixes and suffixes, along with words that let it know this is not a name but, rather, something like "Help Desk." If you think of one we've missed, add it to whichever list it fits into (in all caps).

Pretty simple really (once Bill finished all the code and handed it over).

All I can think when I use this is, "Damn. I wish I'd given him something hard to do."

1. Barb10/17/2006 10:22:20 AM

I have a field on a form that pulls the username from the address book. When I apply this formula to another field on the form, it doesn't pull the last name properly for a person whose firstname is Maria and last name is Armstong Murphy. The formula results in Murphy, Maria Armstrong when it should be Armstong Murphy, Maria. Can you help?

2. Scott Good10/17/2006 03:20:37 PM
Homepage: http://www.scottgood.com

Hi Barb,

Ugh. You could just hypenate her name

Seriously, though, the code has reserved words (like "van") that it recognizes as patterns that might be part of a multiple-word last name.

The problem, of course is "Armstrong" is a pretty common last name in and of itself but adding "ARMSTRONG" to the list of "DblLastNames" variables (put it all in caps) will probably take care of the problem...as long as you don't have anybody with that as a middle name.

If that doesn't work you could hack it with something built around Maria's specific name (hey, I SAID it was a hack).

Or, hyphenate...

3. John Dillon06/27/2007 12:58:17 PM
Homepage: http://www.perforce.com/wrt

I've got a "Person Lookup Class" script library to handle this problem once and for all. Look for it in an upcoming edition of Lotus Advisor magazine. It's a new and improved version of the one I presented at the Advisor Summit last August in Phoenix. If you can't wait, check the Advisor site for ways to download the article and old code.

Basic approach is to look up the name in the NAB, then grab the values out of the last name and first name fields and build a new "Last, First" value.

I do it in LotusScript, but you could undoubtably create JavaScript and Formula variations.

Rally on!

John

4. Scott Good06/27/2007 03:37:52 PM
Homepage: http://www.scottgood.com

Hi John,

I'm looking forward to that article. The first one in your series on writing classes in LotusScript was great. Having said that, what you're talking about, while it would be terrific to have, is a bit different from what we were doing here.

In ours, we were trying to solve the problem of correct last-name-first sorting (1) in a view and (2) from a single field containing the entire name including prefixes and suffixes. Because of the view, we were stuck with @Formulas (and built it long before you could do looping).

Looking forward to your next installment...

Scott

5. jazzy07/07/2008 01:06:58 AM

I have some big problem till yesterday it was okay but today i opened my domino 6.5 server loged in as administrator when i click people on the left pane of console the list of people apearing wrongly first name is not appearing first and the last name is appearing first which is not desired one for my company coz when user open the address book they see last name first and they are killing my brain out please help me out init