A guide on how to handle SharePoint Lookup Columns.
The SharePoint (ClientAPI) provider has an inbuilt feature to automatically match and link lookup columns based on the value associated with the list.
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.
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.
Connect to the source (Lookup Data we created before) and to the target (SharePoint list), and then map the columns as below.
Run the comparison and synchronise the results to add the items to the list. This will automatically connect the lookup for any future additions.
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.
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
Use the Data Preview feature in Data Sync to see the data that SharePoint returns.