Ben Langhinrichs

Photograph of Ben Langhinrichs

E-mail address - Ben Langhinrichs

Recent posts

Tue 19 May 2020

An unladen user

Thu 7 May 2020

Can we get a huzzah for updated Domino Limits?

Wed 29 Apr 2020

A bigger boat: when in Rome

July, 2020
   01 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:

251.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!

251.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.

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

How about:



- Julian