Handling SharePoint Lookup Columns

There are multiple types of columns in SharePoint, some of which depend upon looking up data in another list. Below we are going to cover how to use Data Sync to write data to these columns and a few of the different options available depending upon your setup.

The article covers general SharePoint lookup columns, People Picker columns and Managed Metadata columns. We also cover how to lookup data in a different SharePoint site if you are not already connected to it.

It's a big topic but you will find loads of useful content under each subheading.

Requirements

Before getting started you need to ensure you have the following:

• Access to SharePoint
• Windows 10 or Windows Server
• Downloaded & Installed Data Synchronisation Studio
• Saved your connections to the Connection Library (makes for easy lookup setup)

Importing Data into SharePoint Lookup - Automatic Lookups

SharePoint Lookup columns, which can either be a single value or multi-value field, will show the data in a user friendly format in the browser. However under the covers SharePoint is actually returning the ID of the item combined with the item name. In order to write data back to these columns you need to present the data in the internal format SharePoint uses (not the user friendly name).

The SharePoint Online and SharePoint Client API connectors have an inbuilt feature to automatically match and link lookup columns based on the value associated with the list. For this to work the value must be an exact match to the value in the defined title column for the lookup in SharePoint.

You can find out which column is the defined column for the lookup under the column settings.

During the Synchronisation the list items are downloaded from the related SharePoint List, Data Sync then indexes this list to get SharePoint's list ID number to assign to the item being linked. This is why the items must be an exact match and they must exist for the link to be successfully created.

If the value does not exactly match, e.g. an email address instead of the user name, then you will need to manually lookup the data.

Below I will show you how to configure your project to use the automatic lookup feature for single items and multiple items.

Connect to your Source and Target

Start by opening Data Sync and connecting to your Source data and your SharePoint site as your Target. In this case we are connecting to a list named Orders and the lookup to the products is configured to use the ProductName column.

This will work for the automatic lookup feature as the source data uses the Product Name the same as the lookup is configured in SharePoint.

Map your Columns

Now map the columns from the source to the target and make sure to select a key column that is unique and can be used to identify each item. For this example the schema map has only two columns; the Order ID and the Products in the order.

Compare & Sync

The next step is to compare the source and target, and preview the data changes to be made. To do this click Compare A > B in the toolbar.

This will return a list of results showing the differences between the source data and the data in SharePoint. Click onto the green plus to view the additions, the blue diamond for the updates, and the red minus for the deletions.

Deletes are disabled by default but will still show in the list if there are any records in your list that are not listed in your source data. To enable deletes set EnableDelete to True in the target connection properties.

As the data looks correct, we can click Synchronise and then Start to begin the sync.

Once that has completed, opening the Orders list in SharePoint shows us that the data has been added successfully.

Multiple Items in a Column

If you have multiple items in a column then the process is the same but you need to ensure that each item is separated by a semi-colon (;). In this example we have a list of orders which contain multiple products. Each product is separated by a semi-colon if there are multiple items in that cell.

Just follow the same steps as described above and your data will be written to SharePoint:

1- Connect to your Source data and to your SharePoint list
2- Map the columns in the schema map
3- Compare the datasets
4- Synchronise

If you are having problems where the data isn't being added to SharePoint, double check what column is being used to lookup into the list in SharePoint and check that the data is an exact match to your source dataset.

Importing Data into SharePoint Lookup Columns - Manual Lookups

SharePoint Lookup columns, which can either be a single value or multi-value field, will show the data in a user friendly format in the browser. However under the covers SharePoint is actually returning the ID of the item combined with the item name. In order to write data back to these columns you need to present the data in the internal format SharePoint uses (not the user friendly name).

Here I am going to show you how to discover the internal format and how you can manually lookup the item ID from SharePoint using Data Sync.

Connect to your Source and Target (SharePoint)

Start by opening Data Sync and connecting to your source data. In this example we have a list of products with a supplier name that we want to add to SharePoint. Our supplier column in SharePoint is a lookup column linked to the supplier list.

Then connect your target to your SharePoint list.

The SharePoint List we are connecting to in this example is a products list where the Supplier is the lookup column. Rather than showing the ID number the column shows the supplier name by looking up into the supplier list on the same site.

Below you can see what the data already in our list looks like:

Reveal the RAW data

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 your SharePoint column, which we will cover how to do this in a moment.

By default Data Sync will hide these column values and just extract the text value, so first you need to disable this by setting TidyLookupData=False on your SharePoint connection. This setting can be found in the connection properties below your target columns in the data source window.

Check the Format

To make sure you get the right format it is best to manually add a value to your list (in SharePoint) and then use the Data Preview feature in Data Sync to see the data that SharePoint returns. This will show you the format that the value is returned, which should be similar to 1;#Value One.

info

Columns with multiple values will be separated by a semi-colon and #, e.g. ID;#Value;#ID;#Value so you will need to lookup each item and return it in this format. You can format multiple values using Dynamic Columns.

Lookup the ID of the Record

Now we need to create a lookup on the source column to return the SharePoint ID of the supplier in the Suppliers list.

The easiest way to do this is by dragging the appropriate SharePoint list from your connection library onto your source column and then configuring the lookup so that the Lookup Data Type and Lookup Target Column matches your source data.

Lookup using the Lookup Button

Alternatively you can configure a lookup to the list manually by using the Add Lookup button in the datasource window. Connect to your SharePoint site using the connection details and make sure to save the connection to the connection library. You need to use the connection library so that the refresh token can be managed otherwise your project will fail after a few days.

Once you have connected select the column with the data to lookup and click onto the Add Lookup button.

Then connect to your SharePoint site and set the Lookup Data Type and Lookup Target Column to be the one that matches your source data. In this example we are matching on SupplierName but this could be a ID value it will just depend on your datasets.

Now we need to create a calculated column which will format the value into what SharePoint is expecting. To create a calculated column click onto the add column button in the source toolbar.

Enter in a name for the column and set the data type. In this example it's titled MySupplier but can be anything so long as it is unique.

You can then enter the function you need and in this example we make use of the FORMAT function to return the data in the format 1;#Value by calling on the lookup ID column and SupplierName from the source:

FORMAT("{0};#{1}",Lookup1_ID,SupplierName)

info

If your source contains null values you will need to handle those. We can expand the function mentioned before and use the following:

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

Now add your calculated column to the schema map and map it to the corresponding SharePoint column. In this example we map the calculated column MySupplier to Supplier

Lookup Manually using Calculated Columns

Another option is to specify the lookup within a Calculated Column function rather than following the steps described previously. For this we can use either LOOKUPA or LOOKUPB. For this example, looking up into SharePoint when SharePoint is the target, we will use the LOOKUPB function.

The syntax for this function is:

LOOKUPB(column, list, WHEN(column, value))

Here we first define the column to return, then specify the list to lookup in, and finally the columns to match. This would be the source column you are using to get the values to lookup and the column from the lookup to match it to.

You can find an example of an expression you might use to do this below. We also cover how to look up data in another SharePoint site or sub-site in this section of the post.

To create a calculated column click onto the add column button in the source toolbar.

Now enter in a name for the new column, this needs to be unique and not already exist in your source, and make sure that the data type of the column is correct for the data you will be returning. Then type in the function to lookup the data.

To lookup the ID in the Suppliers list (found in the site we are connected to in the target) where the SupplierName is equal to the CompanyName in the suppliers table the function we would use is:

LOOKUPB("ID","Suppliers",WHEN("CompanyName",SupplierName))

This is logically equivalent to the SQL statement:

SELECT ID FROM Suppliers WHERE CompanyName = ? 

We can then take this a step further and format the value in the same statement. The FORMAT function will return the values in the format specified. So for this column we need to return the ID followed by ;# followed by the name of the supplier.

The full statement, including the lookup, will now look similar to:

FORMAT("{0};#{1}",LOOKUPB("ID","Suppliers",WHEN("CompanyName",SupplierName)),SupplierName)

Now add your calculated column to the schema map and map it to the corresponding SharePoint column. In this example we map the calculated column MySupplier to Supplier

Compare and Sync

Click the Run Compare button in the toolbar to run the compare and preview the results to sync.

We can see that there are 73 records to add to SharePoint and 4 to update. Clicking on to the green plus will show the data that is being added, and the blue diamond will show the records to be updated with any changes highlighted in yellow.

Please note deletes are disabled by default but will still show in the list if there are any records in your list that are not listed in your source data.

When you are ready to add the data to SharePoint click Synchronise and then Start to begin the sync.

The records are now visible in the list and the lookup for the supplier name was successful.

Working with People Picker Columns - Lookups into the UserInformation List

The People Picker column is essentially a lookup to another list in SharePoint, specifically into the User Information List. In your browser SharePoint will return the title column for each user, but behind this it is actually returning the ID and title of that item in the format ID;#Value. The User Information List is only accessible from the Root Site so you will need to have created and saved the connection to your root site in the connection library.

To write back to this column you need to lookup the ID and return the data in the format SharePoint expects ID;#Value.

We recommend checking that this format is correct by previewing data inside Data Sync, just make sure there is one record already added to your SharePoint list (you might need to add this manually). To preview the internal SharePoint format you need to set the Connection Property TidyLookupData to False and then preview the data using the preview button either in the datasource toolbar or in the schema map toolbar.

Your value should look similar to 1;#User.

We will need to return the data to be added in this format after looking up the corresponding ID. To do this we will lookup the data we need inside the User Information List from the root site and then format it using a Calculated Column.

Lookup User

You might have your users listed in your source by their name or their email address. If you have the users listed with their full name (as it would appear in SharePoint with the TidyLookup set to True) then you can simply map this column to the field (e.g. assigned to) and Data Sync will do the rest for you. See the article on automatic lookups.

However for this example the users are listed by their email in the source dataset and this will not automatically match up.

We need to lookup the users email addresses in the User Information List and then format this into the format SharePoint is expecting. The User Information List is usually in the Root site, in this example the list we are connected to is in a sub-site so we need to do a lookup into the root to get the data we need.

To do this you need to have created and saved the connection to your Root site in the connection library.

The easiest way to create the lookup is to drag the User Information List from your connection library (this will only be visible if the saved connection is to your root site) onto your source column, and then configuring the lookup so that the Lookup Target Column is set to the email address.

Alternatively you can configure the connection during the lookup by using the Add Lookup button in the datasource window .

Lookup using the Lookup Button

To add the Select the column with the data to lookup and click onto the Add Lookup button.

Then connect to your SharePoint site and set the Lookup Target Column to be the one that matches your source data. In this example we are matching on Work email but this could be a different value it will just depend on your datasets.

Format the Value

Now we need to use the lookup values to return the data in the SharePoint internal format. To do this create a calculated column and use the FORMAT function format the value to be what SharePoint is expecting e.g. ID;#User.

The example below uses the values returned from the lookup made earlier.

FORMAT("{0};#{1}",Lookup1_ID,Lookup1_Name)

However if your source contains null values you will also need to handle those. So we can expand on the function created previously:

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

Configure the Schema Map

Now add your calculated column to the schema map and map it to the corresponding SharePoint column. In this example we map the calculated column fx_User to Assigned to

Compare & Sync

Click the Run Compare button in the toolbar to run the compare. We can see that there are 4 records to add to SharePoint and clicking on to the green plus will enable us to preview the data that is being added to make sure it is correct.

If there are any updates then click onto the blue diamond to show the updates to be applied. Please note deletes are disabled by default but will still show in the list if there are any records in your list that are not listed in your source data.

When you are ready to add the data to SharePoint click Synchronise and then Start to begin the sync.

The records are now visible in the list and the lookup for the users was successful.

Adding Data to a Managed Metadata Column (TaxonomyList)

Managed Metadata columns are handled in a similar but slightly different way to other lookup columns in SharePoint. To write back to these columns they require the Guid (IdForTerm) and the name of the term formatted as Name|Guid.

In this example we have a SharePoint list that has a Managed Metadata column which returns Job Titles. The roles are defined and listed in the TaxonomyHiddenList in SharePoint.

Start by connecting to your Source Data and to your SharePoint list as your target.

If you manually update a row in SharePoint you can preview the data to see what format is expected. This should match the Name|Guid format mentioned earlier. This data can be found in the SharePoint internal column which will have your column name followed by _0 and an internal name of a guid e.g. ManagedMetadata_0.

Lookup

We now need to lookup the Guid for each row item. In this example our Managed Metadata is Taxonomy Data that returns the role e.g. Director, so we need to lookup the ID (guid) of the role in the Taxonomy Hidden List. The Taxonomy Hidden List is only available in the Root site so you will have needed to saved the connection to your root site in the connection library.

If you have only a single item in each row then you can use the following method. Otherwise if there are multiple items to be returned you will need to use Dynamic Columns which is described in the next section.

The easiest way to do the lookup is to drag and drop the list onto the column in your source you want to lookup. In this example we drag the TaxonomyHiddenList onto the Role column.

This will open the lookup configuration window where we can

Format the Data

We then need to use the FORMAT function and calculated columns to combine the Role and IdForTerm returned in the lookup, which should look something like this:

FORMAT("{0}|{1}",Role,Lookup1_IdForTerm)

The format function returns the name of the item followed by a | followed by the corresponding guid for the specific item.

You can then add this column to the schema map.

Handle Multiple Items

If your data has multiple items listed in a row, then you can handle the lookup and formatting of each item using Dynamic Columns.

SharePoint separates these by semi-colons however if your source data is separated with a different separator you can define that within the code. For this example we have used a comma to separate each item in the source data.

The code you could use for this, changing the name of the items where applicable, can be found below:

partial class DataSourceRowOverride : Simego.DataSync.DynamicColumns.DataSourceRowInternal //Do Not Change This Line
{
    public string fx_Role { get; set; }
    
    public override bool BeginRow()
    {
        fx_Role = string.Empty;
        foreach(var term in Role.Split(','))
        {
            if(!string.IsNullOrEmpty(fx_Role))
                fx_Role += ";";
            
            fx_Role += FORMAT("{0}|{1}", term, LOOKUPB("IdForTerm", "TaxonomyHiddenList", WHEN("Path", term)));
        }
        
        return true; // return false to skip row from results.
    }

}

This creates a column titled fx_Role and splits each item at the comma into a term which can then be processed one by one. This is then rejoined into the correct format and separated by a semi-colon as SharePoint requires.

You can now map this to the schema map as described below.

Configure the Schema Map

Once you have created your lookup either using Calculated Columns or Dynamic Columns depending on your dataset, you need to map this to the internal SharePoint Column for your Managed Metadata (usually ends with _0).

In this example this is ManagedMetaData_0. Make sure to select a key column that is unique and can be used to identify each record, and your schema map should look similar to the below image:

Compare and Sync

Click the Run Compare button in the toolbar to run the compare and preview the results.

We can see that there are 3 records to add to SharePoint and clicking on to the green plus will enable us to preview the data that is being added to make sure it is correct.

If there are any updates then click onto the blue diamond to show the updates to be applied. Please note deletes are disabled by default but will still show in the list if there are any records in your list that are not listed in your source data.

When you are ready to add the data to SharePoint click Synchronise and then Start to begin the sync. Your records will now be added to your SharePoint list.

Lookups from a SharePoint Subsite

Sometimes you might be connected to a sub-site of your main SharePoint site but need to access data inside the Root (main) site. For example the TaxonomyHiddenList can only be found within the root site.

Due to the need to use OAuth to connect to SharePoint you can only use the connection library to perform a lookup. This is because you need a valid refresh token for each site, which is managed by the connection library.

If you have already saved the connection to your root site or other subsite, then you can locate the site in the connection library list and drag the required list onto your source column with the values to lookup. You can then use the lookup within calculated columns to return the value in the expected format.

If you haven't saved the connection then you will need to use the lookup button to create and save the connection, then use calculated columns to return the data in the expected format

Drag and Drop

To create the lookup from the connection library drag and drop the list onto the source column to be looked up. In this example we drag the TaxonomyHiddenList from the Root site connection onto the source column Role

This will then open the lookup configuration window. Make sure to check that the Lookup Data Type and Lookup Target Column are correct. For this example the Path is the column that links to our source data Role which are string data types.

Using the Lookup Button

If you haven't already saved the connection to your select the column in your source to lookup and click the lookup button to create a lookup.

This opens the connection window where you can now connect to your SharePoint site. Just enter in the URL, authorise the connection and select the list from the dropdown. In this example we are connecting to TaxonomyHiddenList.

Click Connect & create library connection to save the connection and open the lookup configuration window.

Make sure to check that the Lookup Data Type and Lookup Target Column are correct. For this example the Path is the column that links to our source data Role which are string data types.

Clicking OK will then add the lookup columns to your source data.

You can now use these columns in your schema map or within calculated column functions e.g. to format the values so that they can be written to SharePoint. This is discussed in more detail in the sections above.

Format Values with Calculated Columns

For managed metadata columns the data needs to presented in the format name|{GUID} (or TermName|IdForTerm), we can handle this by using calculated columns.

Add a calculated column to your source by clicking on the add column button in the data source window.

Enter in a name for the column (this must be unique) and make sure the data type is set to string. Then using the FORMAT function we will configure the value so it is in the format TermName|IdForTerm.

Below is the simple expression needed to do this where the TermName and IdForTerm are separated by a bar(|).

FORMAT("{0}|{1}",Role,Lookup1_IdForTerm)

Once you have created your calculated column, you can add the column to your schema map or use it in another function in your project.

SharePoint User & Group Columns

SharePoint User/Group Columns are just like normal SharePoint Lookups except the target list is the User Information List in the current site.

When attempting to set a User/Group value to a list item that user must exist in the User Information List so that it has an ID number than can be referenced in the lookup.

You cannot simply sync users into this list it must be done via an elevated API call so that its created correctly in the SharePoint Database. Therefore you should ensure that your users are added as individual user accounts under the SharePoint site permissions.

Using LOOKUPA/B Function

The process to follow is the same as the previous examples where you can use the LOOKUPA/B calculated column function to get the ID of a User from the User Information List.

LOOKUPA("ID", "User Information List", WHEN("Name", value))

You will need to return the value in the format SharePoint is expecting. So we can extend the above lookup function to convert this into the SharePoint Lookup value syntax as well:

IF(ISNULL(LOOKUPA("ID", "User Information List", WHEN("Name", value))), NULL(), FORMAT("{0};#{1}", LOOKUPA("ID", "User Information List", WHEN("Name", value)), value))

If you have any questions about any of the topics covered in this article or you're not sure how to relate this to your project/scenario then please send us an email and we can guide you.