I am working on this year's Lotusphere sessions database, and had a request for sorting the speakers by last name. Since the Speaker 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(Speaker; " ")+", "+@LeftBack(Speaker; " ")
which works great until you see session AD218, which is being presented by "Cees van der Woude" and "Peter Janzen". 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 STR115, which has a speaker named "There will be a second speaker from my group, TBD. x"? How do we handle "TBD", which several sessions still have? If this were a single value, the formula could handle these exceptions more easily, but the multi-value list makes it very hard...
Except for @Transform, which Rocky Oliver wrote about this week, 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.
After some playing around, I came up with the following, which may look complicated, but which basically makes anything with the string "TBD" in it into a category "TBD" and then looks for the a match for the second word to see if it starts with a lowercase letter, and so on.
Special Formula #1
@Transform(Speaker; "S";
@If(S = "TBD":"" | @Contains(S; "TBD"); "TBD";
@Matches(@Left(@Word (S; " "; 2); 1); "{a-z}");
@Word (S; " "; 2)+@RightBack(S; @Word (S; " "; 2))+", "+@LeftBack(S; @Word (S; " "; 2));
@Matches(@Left(@Word (S; " "; 3); 1); "{a-z}");
@Word (S; " "; 3)+@RightBack(S; @Word (S; " "; 3))+", "+@LeftBack(S; @Word (S; " "; 3));
@RightBack(S; " ")+", "+@LeftBack(S; " ")));
After a bit more looking, I realized I wasn't using the @Matches efficiently, so I changed the logic to match the whole string by adding an asterisk at the end:
Special Formula #2
@Transform(Speaker; "S";
@If(S = "TBD":"" | @Contains(S; "TBD"); "TBD";
@Matches(@Word (S; " "; 2); "{a-z}");
@Word (S; " "; 2)+@RightBack(S; @Word (S; " "; 2))+", "+@LeftBack(S; @Word (S; " "; 2));
@Matches(@Word (S; " "; 3); "{a-z}");
@Word (S; " "; 3)+@RightBack(S; @Word (S; " "; 3))+", "+@LeftBack(S; @Word (S; " "; 3));
@RightBack(S; " ")+", "+@LeftBack(S; " ")));
Still, this didn't seem very elegant, and wouldn't handle Cindy Lou Who van der Pelt, for example. Then, inspiration hit. Why not use a nested @Tranform? (Yes, Rocky, I got carried away)
Final Formula
@Transform(Speaker; "S";
@If(S = "TBD":"" | @Contains(S; "TBD"); "TBD";
@Do(R:=@Transform(@Explode(S; " "); "W";
@If(@Matches(W; "{a-z}*"); W+@RightBack(S; W)+", "+@LeftBack(S; W); @Nothing));
@If (@Trim(R) != ""; @Trim(R)[1]; @RightBack(S; " ")+", "+@LeftBack(S; " ")))));
Top that, Rocky!
OK, Rocky, where is your example with nested @Transform's? Do you have a more elegant and powerful formula to show?
Copyright © 2004 Genii Software Ltd.