I 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 RequirementThe 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 LittonCindy Lou WhoMary Jane van der WeltenTom DuffBen LanghinrichsHarry Belafonteshould be listed as
Harry BelafonteTom DuffBen LanghinrichsJoe LittonMary Jane van der WeltenCindy Lou WhoThis requires an adaption using the @Sort method, and a bit of slight of hand.
Solution to New Requirementsorted_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.
Technorati tags: Show-n-Tell Thursday SnTT