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.

SharePoint Lookup Columns

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.

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

Assuming you have added a lookup to your data source back to SharePoint to get the List ID value for the related item you can build the value like this.

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

Or even via in-line LOOKUPA/B functions

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

Manual Lookup

Demo showing how to manually create a lookup to assign a SharePoint item that links to a item in another SharePoint List.