How To

Handling SharePoint Lookup Columns

A guide on how to handle SharePoint Lookup Columns.

Understand the Lookup Column Feature

The SharePoint (ClientAPI) provider has an inbuilt feature to automatically match and link lookup columns based on the value associated with the list.

Connect to SharePoint Client API

In this example we have created an Orders list in SharePoint and added a multi-value column that will contain the products that relate to this order.

SharePoint List

Setup the Lookup Data

To handle the lookup data we simply need to provide the name of the lookup value separated with a semi-colon(;) i.e. here we created an Excel file with the order details.

Lookup Data

Setup the Data Sync Project

Connect to the source (Lookup Data we created before) and to the target (SharePoint list), and then map the columns as below.

Schema Map

Compare and Sync

Run the comparison and synchronise the results to add the items to the list. This will automatically connect the lookup for any future additions.

SharePoint List

Add Item

How it works

During the Synchronisation we download the list items from the related SharePoint List and index into this list to get SharePoint's internal identifier number to assign to the item being linked. Therefore items must be an exact match and they must exist for the link to be successfully created.

If your Lookup is simply a single item then you just need to return the Name/Title of that item.

The connector will attempt to connect lookup columns automatically when the related list is in the current site. It will do this by retrieving the list values from the target list and doing a exact text match based on the supplied value and the value of the Title field in the related list.

Manual Lookup Columns

You can also manually apply the Lookup value by getting the ID number of the related item and build the standard SharePoint Lookup reference.

SharePoint uses an internal format for Lookup Values similar to 1;#Value One where the number represents the ID value of the item in the related list and the text is the SharePoint Title field of that item. To set lookup values you need to build this string and map it to the SharePoint column.

Data Sync by default will hide these column values and just extract the text value, so first you need to disable this by setting TidyLookupData=False.

Use the Data Preview feature in Data Sync to see the data that SharePoint returns.