Ben Langhinrichs

Photograph of Ben Langhinrichs

E-mail address - Ben Langhinrichs

Recent posts

Thu 29 Apr 2021

Archive a Notes DB off-line w/ Field data and active content

Tue 20 Apr 2021

Archive a Notes DB off-line in 4 easy steps

Thu 18 Mar 2021

Preservation of all the tiny details

May, 2021
02 03 04 05 06 07 08
09 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31

Search the weblog

Genii Weblog

Top that, Rocky!

Thu 16 Dec 2004, 01:46 PM

by Ben Langhinrichs
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.

What has been said:

252.1. Rob McDonagh
(12/17/2004 06:04 AM)

Am I the only one who thinks this challenge could be turned into a really cool segment of Rocky's formula session? Heck, you could make a whole session out of programming challenges between various experts. Give them a problem, lock them in a room for an hour, and then have them present the solutions to an audience. Damien could judge who did better. That would be fun to see!

252.2. Ben Langhinrichs
(12/17/2004 06:17 AM)

Cool. We probably couldn't do it in the session itself, but Penumbra will have a suite, and perhaps we could schedule it there for later.

252.3. Julian Robichaux
(12/17/2004 03:11 PM)

How about:



- Julian