Notes Formulas for Strings and Lists

I've spent quite a few hours recently on an application that turns out to have some rather heavy reliance on string and list manipulation. In this regard I'm always impressed with what a developer can do with @Formulas. Sometimes the heavy lifting can come from the pair-wise concatenation capabilities of the Notes Formula Language. Take the following for example:

Say I have a field on my form called "System" and it contains one of the following: WP, SS, PG. (WP=Word Processing, SS=Spreadsheet, PG=Presentation Graphics) The following five lines will find the Technical Lead for whatever value I have in my "System" field.

1) @Set("_System";"WP":"SS":"PG");
2) @Set("_Leads";"Larry":"Curly":"Moe");
3) @Set("_NewList";@Explode(@Transform(_System + ";" + Leads;"x";@If(@Word(x;";";1)=System;x;@Nothing))));
4) @Set("_Count";(@Elements(_NewList)-1)*(-1));
5) @Subset(_NewList;_Count)


Part of the third line combines the two lists into a new list (_System + ";" + _Leads) using pair-wise concatenation...

WP;Larry
SS;Curly
PG;Moe

The condition parameter of the @If formula in line three examines the first word of each item in the new list and the @Transform keeps it if it matches the value in the System field, or removes it using @Nothing it if it doesn't match. The resulting new list...

WP
Larry

created using @Explode subsequently has its first member removed using the @Subset in line five, leaving me with Larry as my Tech Lead for Word Processing. (Note: counting the elements in line four allows me to assign multiple Tech Leads to any System)

The linchpin in this is your two lists must be matched up one-to-one, otherwise the pair-wise concatenation will create a mis-matched new list. Also, sometimes the combination of @Explode and @Implode can help you manipulate your list before you run it through another @Formula like @Transform or @Replace.

One word of caution when doing operations on strings and operations on lists. Sometimes the string operators will work on lists, and if you are wanting to make exact matches on elements, be sure to use the right @Formula. For example, @Contains will check for the presence of a value, but if you have ambiguous values (Frank vs. Franklin), you may not get the results you are expecting. Try @IsMember instead. Same is true for @ReplaceSubstring and @Replace.