Genii Weblog

SNTT: Sorting by last name first the right way

Thu 29 Jun 2006, 09:49 AM



by Ben Langhinrichs
Show and Tell ThursdayI posted this long ago in a couple of competitive bouts with Rocky, but they are handy tips and might well have gotten mised by those who might use them as opposed to just watching the show.  This requires @Functions which were introduced in ND6, so don't try it with R5.

Original Requirement (Categorized View)

Let's assume you have a database with people's names contained in multi-value text list field called Attendees.  Now, you want to categorize documents by attendee, so you can see which sessions Roger Hebert is in, for example.  Since the Attendees field is stored as a multi-value list, such as "Thomas Duff":"Joe Litton", this would seem easily handled by a categorized column with a formula such as

Original Formula

@RightBack(Attendees; " ")+", "+@LeftBack(Attendees; " ")

which works great until you see attendees with names such as "Cees van der Woude".  Well, we don't want the "Cees van der Woude" to be returned as "Woude, Cees van der" because that would be both inaccurate and culturally ignorant.  So, how do we handle this?  Also, how do we handle entries such as "N/A", which leaders put for any person for whom they didn't get a correct name?  If this were a single value, the formula could handle these exceptions more easily, but the multi-value list makes it very hard.  Also, there are a few really odd names such as Cindy Lou Who van der Pelt.

But in Notes 6, the @Transform function was added, which does indeed allow us to act as if this is a single value instead of a list.  Let's see if we can come up with a formula for our categorized column that handles these exceptions cleanly.  

A much better Solution to Original Requirement

The following column formula is used in the column to be categorized, with the sorting handled by the view engine.

@Transform(Attendees; "A";
@If(A = "N/A":""; "N/A";
       @Do(R:=@Transform(@Explode(A; " "); "W";
          @If(@Matches(W; "{a-z}*"); W+@RightBack(A; W)+", "+@LeftBack(A; W); @Nothing)); 
                  @If (@Trim(R) != ""; @Trim(R)[1]; @RightBack(A; " ")+", "+@LeftBack(A; " ")))));

That works well, and will handle all of the special cases, so long as the initial words in last names, such as "van" in "van der pelt" start with lowercase letters.

New Requirement (List displayed in a document)
But then you decide that inside each document, you would like the list sorted by last name first as well, but not shown that way.  In other words:

Joe Litton
Cindy Lou Who
Mary Jane van der Welten
Tom Duff
Ben Langhinrichs
Harry Belafonte

should be listed as

Harry Belafonte
Tom Duff
Ben Langhinrichs
Joe Litton
Mary Jane van der Welten
Cindy Lou Who

This requires an adaption using the @Sort method, and a bit of slight of hand.

Solution to New Requirement
sorted_by_last := @Sort(@Transform(Attendees; "A";
@If(@Do(R:=@Transform(@Explode(A; " "); "W";
          @If(@Matches(W; "{a-z}*"); W+@RightBack(A; W)+", "+@LeftBack(A; W); @Nothing)); 
                  @If (@Trim(R) != ""; @Trim(R)[1]; @RightBack(A; " ")+", "+@LeftBack(A; " ")))));
sorted := @Trim(@RightBack(sorted_by_last; ",")+" "+@LeftBack(sorted_by_last; ","));

Yeah, just what you were thinking, right?  The key is using the @Transform we used above, but then recreating the original after sorting.  Geeky, but effective.

Copyright © 2006 Genii Software Ltd.

What has been said:


468.1. Richard Schwartz (06/29/2006 10:33 AM)

Nice solution, but the assumption that last name prefix parts will be all lower case is not a good one unless you are enforcing it somehow... and that could put you right back in that "culturally insensitve" position.

The only foolproof mechanism is to use separate fields for first and last name.


468.2. Ben Langhinrichs (06/29/2006 11:21 AM)

Without a doubt, the only correct way to do this is with separate fields, but this is a quite common occurrence, and in most places I have seen it, here and abroad, the lower case rule has been enforced. Sometimes it has not, and I guess you could enhance this some with "common" middle parts, such as "von" and "van" and "de" that are no commonly first names.


468.3. Richard Schwartz (06/29/2006 01:14 PM)

Bill Ernest had code that did exactly that. I'm pretty sure that Scott posted it at one point as one of his tribute tips following Bill's.

BTW: In a lot of cases, it would be us Americans who violate the lower case rule. E.g., "Dick Van Dyke", rather than "van Dyke". "Dick Van Patten", too. And I think most Van Dykes and Van Patten's here in the US use the same spelling. But there are also legitimate cases in European countries where it would be incorrect to use lower case. I had a classmate whose last name was "Schach von Wittenau" -- a truly maddening case for us software folks because one of the prefix parts is lower case but the other isn't!


468.4. Scott Good (07/12/2006 05:36 AM)

Hm. The HTML didn't come out very well in my last post. Sorry about that.

Scott