Sometimes you may need to specify a specific URL to the SharePoint list you want to perform a lookup on, if that list is not in the site specified in Data Source A. For example if Data Source A was running in a sub-site of SharePoint and the required list is located in the root site of SharePoint Data Syncs Lookup functions can handle extended formats that override the settings that are typically gathered from the data source properties. Using calculated columns we can use the LOOKUP function to pass the SharePoint site as part of the expression.
LOOKUPA(string column, FROM(KV(string parameter, string location/url), KV(string valueParameter, string valueName)), WHEN(string value, object))
|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.|
In this example we can get the User ID from the User Information List in the root site whilst running in the sub-site.
LOOKUPA("ID", FROM(KV("SharePointUrl","https://simegoltd.sharepoint.com/"), KV("ListName","User Information List")), WHEN("Name",Title))
A Demo video showing how to create a lookup to assign a SharePoint item that links to a item in another SharePoint List. It covers 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 when trying to sync the data.