Formulas that deal with multi-valued items or multi-valued results
November 17, 2010
Posted by on
Everyone knows that Notes Migrator for SharePoint has the ability to run formulas (using the Lotus @Formula language) to transform data as you migrate it. This feature is used for a variety of things ranging from concatenating two fields to translating status code field values to using an @DBLOOKUP to “join” data from a different view. People are sometimes unclear, however, how they would use this facility to process multi-valued data.
There are two parts to this problem:
1. How to create a formula that yields multi-values items:
There are many constructs in the Notes formula language that give multi-valued items as results. The simplest is to just use a colon “:” to concatenate text values into a text array. Two examples are shown below:
‘A’ : ‘B’
@Uppercase(Region) : @Lowercase(Region)
Also note that many @Formulas will yield a multi-valued result if the input was multi-valued to begin with. For example, if Categories is a multi-valued item, then this formula will yield a multi-valued result:
Be sure to specify the MULTI option in your Source Data Definition column so that the query engine extracts multi-valued data.
2. How to map multi-valued columns to SharePoint fields:
Only three field types in SharePoint allow multiple values (Choice, Lookup and User). For these, you need to specify MultiValue in the appropriate Target Data Definition field for NMSP to attempt to write data that way. (If you are writing to an existing target, you may need to modify the column properties in List Settings as well.)
For other SharePoint field types (such as Text items) you have to make a choice as to whether you want to concatenate the multiple values into a single value, take just the first value, or take the last value. You can control this option via your detailed mapping options.