How To

Importing Data to SharePoint Lookup Columns

If you need to write data to a SharePoint lookup column you will need to present the data in the format it is expecting. For SharePoint Online you will need to manage the lookup columns manually.

Data Sync makes manual lookups easier by enabling you to preview the raw data so you know exactly what is expected. You can then use calculated columns to configure the format to match.

For SharePoint Online you need to manually configure the ID you are passing by getting the ID number of the related item and building the standard SharePoint lookup reference for that item.

The video below shows the process of creating a lookup to assign a supplier to a product in a lookup column. It shows how to format the value so that it is in the format SharePoint is expecting, and how to diagnose and resolve some of the problems you might encounter such as Null values.

Connect to your Source and Target (SharePoint)

Start by opening Data Sync and connecting to your source data. In this example we have a list of products with a supplier id that we want to add to SharePoint. Our supplier column in SharePoint is a lookup column that will return the name of the supplier.

Source Data

Then connect to your SharePoint list.

Reveal the RAW data

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. This can be found in the connection properties below your columns in the data source window.

Tidy Lookup Data

Check the Format

To make sure you get the right format it is best to manually add a value to your list and then use the Data Preview feature in Data Sync to see the data that SharePoint returns. This will show you the format that the value is returned, which should be similar to 1;#Value One.

Lookup your Supplier

Create a lookup on your source column to look the value up in the SharePoint list. You can do this by dragging the appropriate SharePoint list from your connection library onto your source column.

Format the Value

Now we need to create a calculated column which will format the value into what SharePoint is expecting. An example of the function you could use is:

FORMAT("{0};#{1},Lookup1_ID,Lookup1_CompanyName")

Calculated Column Format

However if your source contains null values you will need to handle those. An example of the function you could use for this is:

IF(ISNULL(Lookup1_ID),NULL(),FORMAT("{0};#{1},Lookup1_ID,Lookup1_CompanyName"))

Calculated Column Format Nulls

Now add your calculated column to the schema map and map it to the corresponding SharePoint column.

Schema Mapping

Compare and Sync

Click the Compare A > B button in the toolbar to run the compare and preview the data by clicking onto the green plus to make sure it is presenting as you expect. If there are any updates then click onto the blue diamond to show the updates to be applied. Please note deletes are disabled by default but will still show in the list if there are any records in your list that are not listed in your source data.

Compare Results

Once you are happy click Synchronise and then Start to begin the sync.

Your values will now be visible in the list.

View Results