Tue 15 Sep 2020
Exciton Boost - Formula(s) for success
Tue 15 Sep 2020
Pushing harder at the limits of formulas
Mon 14 Sep 2020
Exciton Boost - Secret Agents protecting your data
SNTT: Sorting by last name first the right way
Thu 29 Jun 2006, 09:49 AMTweet
by Ben Langhinrichs
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
@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.
Copyright © 2006 Genii Software Ltd.
What has been said:
469.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.
469.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.
469.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!
469.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.