Using Lookups with SharePoint in Data Sync

SharePoint Client API Connector

The Client API 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.

Setting Data in SharePoint Lookup Columns

SharePoint uses an internal format for lookup values similar to 1;#Value 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.

If you already have data in your column you can preview the data to check the format SharePoint is returning. To do this click onto the preview button in the data source window.

If you do not have any data in your column then we recommend manually adding a row so then you can preview and check the format SharePoint returns.

Format the Value

You will most likely need to lookup the ID of the value from SharePoint. You can do this either by drag and dropping from the connection library or by using the lookup button in the data source window toolbar. You can read more about how to do a lookup in Data Sync here.

You can then use your LookupID column in function in calculated columns to return the value in the correct format and return null is there is no value. This function would look similar to:

IF(ISNULLOREMPTY(Lookup1_ID), NULL(), FORMAT("{0};#{1}", Lookup1_ID, Lookup1_Title))

Alternatively you can do an in-line LOOKUPA/B function and format the value at the same time.

A function for this whilst also returning Null if there is no value would look like:

IF(ISNULLOREMPTY(LOOKUPB("ID", "MyList", WHEN("Title", myValue)), NULL(), FORMAT("{0};#{1}", LOOKUPB("ID", "MyList", WHEN("Title", myValue)), myValue)))

Lookup into a Different SharePoint Site or List

If you need to connect to a different site or list to the one that you are connected to in your project you can specify the SharePoint site or list url either by performing a manual lookup, using calculated columns, or drag and dropping from the connection library.

Manual Lookup

To do a manual lookup select the column in your source to lookup and then click the lookup button in the data source toolbar menu.

This will then open the connection window. Connect to your SharePoint site and select the list the values should be looked up in.

You will now be presented with the Lookup Connection window where you need to select the target lookup column to link the data and make sure the data type is correct. You can change the name of the lookup and any other settings if you need to.

Once the lookup has loaded you can use any of the columns by expanding the list and adding them to your schema map.

Drag and Drop Lookup

If you have the other SharePoint site saved to your connection library then you can select the list you want to lookup in and drag this onto your column with your values to look for.

Select the key column, set any additional properties and then click OK to create the lookup.

You can then use any of the columns by expanding the list and adding them to your schema map.

Calculated Columns

You can lookup a value in a different site or list using Calculated Columns by specifying the list/site. To open the calculated column window click on the button in the data source window.

Syntax

The syntax for the function which can be for either LookupA or LookupB, depending on your project, is:

LOOKUPB(string column, FROM(KV(string parameter, string location/url), KV(string valueParameter, string valueName)), WHEN(string value, object))

Inputs

Below is a table of the inputs you can use in the lookup function and their descriptions.

Example Functions

To lookup in a different site, your function might look similar to:

LOOKUPB("ID", FROM(KV("SharePointUrl","https://companyabc.sharepoint.com"), KV("ListName", "Manager")), WHEN("Title", JobTitle))

To lookup in a different list, your function might look similar to:

LOOKUPB("ID", "Manager"), WHEN("Title", JobTitle)

To get the User ID from the User Information List in the root site whilst the project is connected to the sub-site in the source:

LOOKUPB("ID", FROM(KV("SharePointUrl","https://simegoltd.sharepoint.com/"), KV("ListName","User Information List")), WHEN("Name",Title))

Demonstration Video

Below is a screen-cast showing how to create a lookup to assign a SharePoint item that links to a item in another SharePoint List, and then format the output to return what SharePoint expects internally. The screen capture shows the process of diagnosing errors you might get when trying to pass the data to SharePoint.