Using Lookups with SharePoint in Data Sync

The following page will guide you through how you can configure lookups to return data from other lists when using Data Sync. You can read more about how to do a lookup in Data Sync here.

The Client API connector will attempt to connect lookup columns automatically when the related list is in the current site. It does 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.

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 open the connection window where you should connect to your SharePoint site and select the list the values should be looked up in.

Once you have connected to your target site this will open the lookup configuration window where you need to select the linking column that connects 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 tree 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.

This will open the lookup configuration window where you need to select the linking column, ensure the data type is correct, and then click OK to create the lookup.

You can then use any of the columns by expanding the tree 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 within the Lookup function. 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.

Object	Data Type	Description
column	String	The column name to find.
parameter	String	The parameter to override.
location/url	String	The location to find the parameter to override with, e.g. a URL.
valueParameter	String	The value you are looking for.
valueName	String	The name of the valueParameter you are looking for.
value	String	The value you are looking for.
object	String	The object you are looking to match the value to.

Example Functions

The examples shown below use LOOKUPB to lookup values using the target columns. If you want to use the source connection/columns then change the function to use LOOKUPA.

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))

Adding Data to SharePoint Lookup Columns

This section briefly covers how you can go about adding values to lookup columns in SharePoint. For a more detailed guide on adding data to lookup columns in SharePoint please see the guide here.

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 setting by making the property TidyLookupData, False. This will then show the format SharePoint expects the data in.

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.

info

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 create and manage lookups 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)))

For more details on adding data to lookup columns in SharePoint please see the guide here.