SharePoint Lookup column brings data into one list from another list in the same site. It is one of the most essential features when you want to create relationships between the two lists in SharePoint. Additionally, you can also govern the Deletion of the data which are already in use. Let’s go into detail.

What is SharePoint LookUp Column?

Definition: In SharePoint, Lookup is a type of column using which you can get the data from another list of the same site (Web).

How do LookUp Columns work? 

Check the picture below. If you want to use data of one list (LIST A) in another list (LIST B) on the same Site (Web) then you can use Lookup Column in the second list (LIST B here).

At the time of creating the Lookup column in LIST B, we need to pick the column from List A for reference

SharePoint lookup column limitations

• You can create a lookup column and fetch data only from the same site, not from the other site.
• If you update anything in the Source list the changes will be present immediately on the destination list.
• You can add multiple columns from the source list to the destination list but when you Enable Multiple Columns, you cannot Enforce Relationship behavior.

How to Create a Lookup column?

We will take an example. We have two lists.

• SourceList
  1. OrderNo (Number)
  2. OrderName (Single line of text)
  3. OrderDate (Date and Time)
• DestinationList
  1. OrderNumber (Lookup)
  2. OrderNumber:OrderName (Lookup)
  3. OrderNumber:OrderDate (Lookup)

Consume data from SourceList to DestinationList.

1. Create a List called SourceList.
2. Create 3 columns (mentioned above) in SourceList. (we are not covering all these steps to create simple list and columns)
3. Now create another list – DestinationList.
4. Go to DestinationList - List Settings >> Create a column
5. Select type of the column as Lookup

6. Scroll down - in additional settings, you need to select a few things as mentioned below.
   1. Get information from: This is your source list from where you want to pull the data.
   2. In this column: This is the column on which you want to put the reference. Basically, this column is the connector key between the two lists. It will match the value and based on that Lookup column can pull the values for other columns too.
   3. Add a column to show each of these additional fields: Here, you can select multiple columns. For each column selected here a new column (Lookup) will be created. You can see here, I have selected 3 columns. One more thing to observe here is, the columns having unsupported datatypes are not even listed here. Please check the next section for supported and unsupported datatypes for Lookup
   
7. Click on OK.
8. You can see here 4 columns. 1 Column (OrderNumber) we created and the rest of the 3 columns we selected for Lookup. Those lookup columns will have a format like this (OrderNumber:columnName), anyways you can change the display name from the list.

9. You can see the source and destination list after adding values to them. SourceList

DestinationList

SharePoint lookup column allows multiple values

In the above example, just a small configuration change and you can pull multiple values in the destination list or library.

• Go to list settings.
• Edit the Lookup column
• In the Additional Column Settings section, tick on Allow multiple values

• After making this change, you can now select multiple values from the source list. While adding or Editing the rows you need to select multiple entries.

• You can see the result; multiple values will be displayed in the view according to the data selected in the Lookup column.

What are the supported data types for the SharePoint Lookup column?

• Single line of text
• Date and Time
• Number

What are the Unsupported data types for the SharePoint Lookup column?

• Multiple lines of text
• Choice (menu to choose from)
• Currency ($, ¥, €)
• Lookup (information already on this site)
• Yes/No (checkbox)
• Person or Group
• Hyperlink or Picture
• Calculated (calculation based on other columns)
• Task Outcome
• External Data
• Managed Metadata

SharePoint lookup column enforce relationship behavior

• Let’s say you have used some data from Source List into the Destination list in the Lookup column.
• Now, after sometimes, you want to delete the records in your Source List, in that case, what will happen to the data that you have already used in the Destination list?
• You can enforce a relationship between the Source list and destination list so that you can govern the DELETE action in the Source list.

Follow the below process to Enforce Relationship behavior for the Lookup column.

1. Go to List settings
2. Click on your Lookup column
3. Edit the column
4. Scroll down till the bottom of the settings page and tick Enforce relationship behavior.

There are two options here.

1. Restrict Delete: If this option is enabled and the User tries to delete a record in SourceList which is already used in DestinationList, then the user will not be able to delete the records and will get a message saying: This item cannot be deleted because an item in the “DestinationList” list is related to an item in the “SourceList” list.

2. Cascade Delete: If this option is enabled and the user tries to delete a record in SourceList which is in-use in DestinationList, the user will get a Popup: Are you sure you want to send this item to the site Recycle Bin?. Once you confirm, then the item will get deleted without any issue.

SharePoint lookup enforce relationship behavior Limitations

SharePoint lookup column enforces relationship behavior will not work if…

• The Lookup column is the site column
• You have enabled “Multiple Values” on the lookup column
• If the lookup column is referencing data from another site.