Lotus Notes to SharePoint Blog

Blog about Dell's Notes Migrator to SharePoint tool and other things related to Lotus Notes migration projects

Migrating Notes keyword fields to SharePoint

Lotus Notes applications have a concept of “Keyword” fields which are basically text fields with a predefined set of possible legal values.  In a data entry form, these may appear as combo boxes, list boxes, check boxes, radio buttons, or pop-up dialogs.  In some cases, Notes developers will even write their own user interface for such fields.  In the following screen shots, the DocType field is implemented as a simple Dialog List.

image

In Domino Designer, we can see how the data entry field is declared.  In this case, it is a single-valued field and the list of possible values is just a list of hard-coded strings. 

image  image

Before going any further, it is important to point out that there is really no such thing as a “Dialog List” data type in the back-end Notes database (and you won’t see any such concept when using Notes Migrator for SharePoint to migrate the content).  If you look at Document Properties, you can see that DocType is really just a Text item.

image

So in Notes Migrator for SharePoint, you could extract this as you would any other text item.  The following screen shots show selecting the database, selecting the data item, and previewing the results.

image  image

image  image

On the SharePoint side, you could choose to map this field to either a “plain” Text column or to a Choice column in your SharePoint List or Document Library.  In particular, if you use our Load From Source Fields function, you will get a simple Text column:

image 

Choice columns.  If you wanted to target a Choice column instead, simply change the Type property to “Choice”.  If the SharePoint list/library you are writing to already has the Choice column defined, it may be programmed with a set of “legal” choices.  In some cases you may want to stick to the list that is there (and even reject records that don’t match the approved list).  You do, however, have the option of using the actual Notes data to automatically extend the list of possible choices on the SharePoint side.  To do this, simply set the AddMissingChoices property to “True”.

image  image

As a reminder, Notes Migrator for SharePoint can actually provision the entire list/library on SharePoint, or it can upgrade the schema of existing lists/libraries by adding additional fields.  But the tool will not convert existing Text columns, etc., to Choice columns.  If you already have a Text column that you want to become a Choice column, you will have to change it manually, or remove the column in SharePoint and let Notes Migrator for SharePoint recreate it the right way.  The following screen shot shows a Choice column provisioned by our tool (with AddMissingChoices enabled).

image

That pretty much covers how to migrate a simple keyword field, but there are several important variations on this that you are likely to encounter in real-world Notes applications…

Multi-valued items.  As discussed in other posts, nobody ever accused Notes databases of being good relational databases and multi-valued items are common.  These types of items are stored as text arrays instead of single text values.

image  image

To migrate the multi-valued items be sure to set the Option property to “Multi” in your source data definition and set the MultiValue property to “True” in your target data definition. 

image  image

Finally be sure to set the MultiValueDisposition property on your field mapping to “All”.  (Unfortunately the Default behavior here is to map just the first value, not the complete array of values.  We plan to change that in an upcoming release.) 

image

(For additional details on writing formulas that deal with multi-valued items, see this post.)

Keyword Aliases.  Some Notes applications differentiate between the way data is stored in Notes and the way it is displayed.  This arrangement is useful in cases where the displayed values might change over time or may be displayed differently for different users (in particular, users who speak different languages).  In the following example, the application stores “1” “2” and “3” for the FAQType field even though user sees “Features”, “HowTo” and “Problem”.  

image  image

While one might choose to continue to store values “1” “2” and “3” in SharePoint and use a Lookup column(see below) to enter and display them, it is more common to simply translate them at migration time.  To do this, simply use a Formula field in your source data definition.  Here is an example formula:   @If(FAQType=”1″;”Features”;FAQType=”2″;”How To”;FAQType=”3″;”Problem”;”Other”)

image 

Note that this is just a special case of “data massaging” that you might want to do as you migrate.  Any valid Notes formula will work here.  You can even use an @DBLookup expressions to merge data from other sources.  (And, yes, I know there are more elegant ways to solve the simple problem above.) 

One more thing to point out here is that some Notes developer may have done this work for you and designed a view column that displays “friendly” values using a formula similar to the one above.  In this case, you could just select the View Column (instead of a data Item) in your Notes Migrator for SharePoint source data definition and simply migrate the pre-computed values from the view index.

Lookup columns.  Sometimes the list of possible keyword values are not hard-coded but instead come from other Notes views or even other Notes databases.  

image

Here are two “Use formula for choices” examples: 

@DbColumn(“Notes” : “NoCache”;”” :”” ; “StateCodes”; 1) 

@DbLookup( “”; “”; “ConfigDocs”; “StatusCodes” ; “Choices”)

Without going into too much detail, the first selects a list of possible choices from the first column in a view and the second looks up a particular configuration document that stores the list of possible choices in a multi-valued text item.

When migrating such a field, you have two basic choices.  First, you could migrate it to a Choice column as described above.  This means that you will no longer be using data documents to control the list of possible choices but will be instead storing the choices as part of the SharePoint list/library schema.  This is a simpler configuration, but you have to be a Designer to modify the choices.

Your other option is to migrate to a SharePoint Lookup column.  Unfortunately Notes Migrator for SharePoint cannot provision this type of column for you so you would have to do a little SharePoint design work yourself.  Once you have set things up, Notes Migrator for SharePoint will happily migrate the data for you.  Here are the steps to migrate to a Lookup column.

  1. Create the List that will contain the set of possible choices.  Of course, Notes Migrator for SharePoint would be a great way to create and populate such a list.  (In the above example, you might migrate the contents of the “StateCodes” view to a similar list in SharePoint.)
  2. Create a List or Library that will contain the main migrated documents.  Add a Lookup column that specifies the above List as the data source.  (As explained above, Notes Migrator for SharePoint can not provision new Lookup columns for you.  It may still be useful, however to use Notes Migrator for SharePoint to design and provision the rest of the list for you.  A good trick is to first migrate a handful of documents using all Text or Choice columns and then change the column type to a Lookup column after the fact.)
  3. Now create your Notes Migrator for SharePoint job.  In your target data definition you can specify the “Lookup” in the Type property for the appropriate column.  When you run this job, SharePoint will reject any records that do not contain legal values so be sure to populate all the possible values in advance (step 1 above).  Of course, the tool will log any exceptions that are encountered.

image   image

2 responses to “Migrating Notes keyword fields to SharePoint

  1. Vishal Marne July 17, 2011 at 9:41 am

    HI ,
    Your Post is very useful for me. i have some doubts relted to dialog list.How we can migrate it into sharepoint as it has properties like single,multiple value choices.

    • swalch July 18, 2011 at 7:35 am

      You need to make sure that
      1. you are extracting the data as a multi-valued item (specify the MULTI option in your source data definition field)
      2. you are writing the data as a multi-valued item (specify True for the Multi Valued property in your target data definition column)
      3. you are mapping all the values (specify All in the Multi Value Disposition property on the mapping tab. Leaving it to Default is usually good enough, but there are cases where you need to explictly specify All)