Enhanced support for Lookup Fields in Notes Migrator for SharePoint 6.2
March 31, 2013
Posted by on
In version 6.0 we added support for migrating content to SharePoint lookup columns. In version 6.1 we added provisioning of new lookup columns. Now here are two new advanced choices for using Lookup columns to help you design more powerful migration jobs.
Add Missing Choices
Normally when you migrate content to a SharePoint List that contains a lookup column, the Notes Migrator for SharePoint code tries to find the data value and correlate it a record in the lookup list. For example, you may be mapping a VendorCode item from your Notes data source to the “Vendor Code” column in your SharePoint target list, which is lookup column that connects to an indexed Vendor Code column in a list containing Vendor records. If you set the new “Add Missing Choices” property in your column definition to true, we will add a new record to the Vendors lookup list if one does not already exist. (This is now very similar to how “Add Missing Choices” works for Choice columns in our tool.)
Note that in order for this to work, the Lookup list should have reasonable default values for all the other columns (especially the ones that are required columns).
Override Lookup Column
A mapping such as the one described above (mapping a VendorCode item from your Notes data source to a Vendor Code column in your lookup list) is fine for simple cases. But sometimes the situation is more complicated than that, and you need to use some other value from the Notes data source to correlate the data.
Suppose for example that the Notes application used VendorTaxID to link things together but the new SharePoint template is designed to use Vendor Codes. With version 6.2 you can now specify an “Override Lookup Column” property to control how Notes Migrator for SharePoint locates the record in the target list to link to. In the following screen shot, note how the Tax ID column in the lookup list overrides the Vendor Code column that is configured in SharePoint. Now you can map VendorTaxId values from Notes and get Vendor Codes in your SharePoint lookup column in the end.
Another great use for this new feature is the case where the Notes application used response documents to link two types of records. For example, it is common to use response documents to link user Comments to a patent document, such as a team’s Action Items (shown below).
In order to correlate the Comment documents to the parent documents, we need to use the internal Notes universal IDs (a.k.a. UNIDs). As luck would have it, Notes Migrator for SharePoint always stores the UNID of each migrated Notes document in a hidden “NotesUNID” column in SharePoint. So you can use that column as your Override Lookup Column and simply map the parent IDs to the lookup column:
Note: this technique works perfectly with the SharePoint Blog site template. The Comments list has a lookup column that references the Posts list. Now you can correlate Comments with Posts using internal Notes UNIDs, which is a lot more reliable than using Titles (which may contain duplicates).