Genii Weblog


Civility in critiquing the ideas of others is no vice. Rudeness in defending your own ideas is no virtue.


Thu 16 Dec 2004, 01:46 PM
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.

Thu 16 Dec 2004, 09:36 AM
Well, I know that many of my readers are up to Notes/Domino 6.5.3 or even the ND7 beta, but there are others still stuck in R4 for one reason or another.  To ensure that everybody is well served, I have added a Domino Limits R4 to go with the, dare I say it, wildly popular Domino Limits page for R5 and ND6.  Anybody who likes is free to bookmark either page, and I'll make sure the URLs keep working.

So, who thinks it is time to start on a Domino Limits for ND7 page?

Copyright © 2004 Genii Software Ltd.