SnTT: @Transform Goodness

If you went to Rocky's session at Lotusphere 2008 on Formula Tricks, you heard about the @Transform function. It lets you take a list of values and run a formula against it to change some or all of the values in the list based on your criteria.

I've used it in two places recently and it works far better than anything we could have done prior to the function becoming available (which was in Release 6). In my first example, I have a list of dates and a list of numbers. I want to sum the numbers only for the dates that are in the future. For example, say the fields are:
SchedDates is 3/24/08:3/31/08:4/7/08:4/14/08
SchedDays is 5:5:4:5

If I look at this on 4/4/08, I want to end up with 9. This is the formula I run:

@If(SchedDates="";0;@Sum(@Transform(@Text(SchedDates) + "~" + @Text(SchedDays);"x";@If(@TextToTime(@Left(x;"~"))<@Today;0;@TextToNumber(@Right(x;"~"))*8))))

That takes the two fields and joins them (eg. 03/24/2008~5:03/31/2008~5:04/07/2008~4:04/14/2008:5), then replaces each one with 0 if the date portion is before today or the number portion if not. So you end up with 0:0:4:5. When you @Sum that, you get 9. Cool!

The other time I used it recently was to make sure that a name was placed in a field as long as it wasn't already in that field or five other fields. That formula looks like this and is in the Input Translation of a field (in this case Level5):

reqNames:=@Name([Canonicalize];"James Q Public/MyCo/US");
@If(reqNames*=Level0:Level1:Level2:Level3:Level4:Level5;@Trim(@Transform(reqNames;"x";@If(@IsMember(x;Level0:Level1:Level2:Level3:Level4:Level5);@Nothing;x)):@ThisValue);
@Trim(reqNames:@ThisValue))

The reqNames field can be multi-valued. If any of the values in the field are in any of the listed fields (Level0, etc.), it replaces that value in reqNames with @Nothing. When all values in reqNames are checked, it prepends the remaining names to the field where the formula lives. If not, reqNames is just prepended to the existing field value, making sure there are no blank entries in the list.

If you haven't already, be sure you take a look at @Transform.

Technorati: Show-n-Tell Thursday