Create list relationships by using unique and lookup columns

Applies to: SharePoint Online, SharePoint Server 2016, SharePoint Foundation 2013, SharePoint Server 2013 Enterprise.

You can form relationships between lists by employing a range of unique columns, lookup columns, and relationship enforcement (cascade and restrict delete); each of which improve your ability to create more intelligent business solutions and aid maintenance of your data integrity.

Overview of list relationships

The following sections offers vital background information about creating relationships between source and target lists, creating unique and lookup columns, enforcing list relationship behaviour, adding primary and secondary lookup columns, viewing, editing, and deleting items within list relationships, managing list relationships when lists have many items, and the additional benefits of using an editing programme, like Microsoft SharePoint Designer 2013.

An overview of list relationships

1. The Items list is the source list and the Orders list is the target list.

2. Each list has distinct columns to guarantee that the right data is connected.

3. The primary lookup column in the source list “looks up” the Order ID column in the target list according to a matching value.

4. A secondary lookup column in the source list instantly adds the Orders Description column from the target list.

Creating unique columns

When you create a column, you can elaborate that the column must contain unique values. Effectively, the list cannot have any same values in that column. You cannot apply a list item that includes a duplicate value, change an existing list item that would cause making a duplicate value, or restore an item from the Recycle Bin if it would lead to a duplicate value. Furthermore, if you produce a lookup column in a source list, and you dictate that column to be unique, the column in the target list cannot contain duplicate values.

Note:  Uniqueness is not case-sensitive, so for example, ORD-231 and ord-231 are considered duplicate values.

A unique column must also have an index. When you create a unique column, you may be asked to create the index and it is instantly made when you press OK. Once a unique column has an index, you cannot delete the index from that column, unless you first reformat the column to allow duplicate values.

The following table summarises what column types can and cannot be utilised to produce unique columns.

Supported Column TypesUnsupported Column Types
Single line of text Multiple lines of text
Choice (single value)Choice (multi-valued)
NumberCalculated
Currency Hyperlink or Picture
Date and Time Custom Columns
Lookup (single value) Lookup (multi-valued)
Person or Group (single value)Person or Group (multi-valued)
Yes/No

Creating lookup columns

To make a relationship between two lists, in the source list, you design a lookup column that retrieves (or “looks up”) one or more values from a target list if those values correspond to the value in the lookup column in the source list. Once you create this lookup column (the primary column), you can keep adding extra columns (secondary columns) from the target list to the source list, given those additional columns have a supported data type described below.

The following table summarises what column types can and cannot be used to create lookup columns.

Supported Column TypesUnsupported Column Types
Single line of text Multiple lines of text
NumberCurrency
Date and TimePerson or Group
Calculated
Hyperlink or Picture
Custom Columns
Yes/No
Choice
Lookup

There are two kinds of lookup columns that you can formulate, a lookup column with an enforced relationship and a lookup column with an unenforced relationship.

A lookup column with an enforced relationship

In a lookup column with an enforced relationship (also called referential integrity), you can lookup single values and maintain the integrity of your data in the target list in one of two ways:

Cascade Delete – When an item in the target list is deleted, the associated item or items in the source list are also deleted. In this case, you want to check that all matched items are deleted within one entire database transaction. For example, if you delete an order in a target list, you can be assured that the compatible order items in the source list is also erased. You don’t want to have order items without a related order, because that would create “orphan” order items that might lead to misunderstandings further on.

Restrict Delete – When you try to delete an item in the target list, and it has one or more related items in the source list, you are prevented from deleting the item in the target list. In this case, you want to ensure that the related item in the target list is preserved. For example, if someone tries to delete an order item from a target list, a customer might still have that order pending, and you want to prevent that delete operation from occurring until the order has been processed. You don’t want the customer’s order to be accidentally deleted because that would affect your ability to complete the order.

Enforcing list relationships

1. A cascade delete operation ensures all related items are deleted in one database transaction.

2. A restrict delete operation prevents deleting items in the target list if related source list items exist.

In short, enforcing a list relationship behaviour helps keep your data valid and helps prevent inconsistencies that might cause problems down the road.

Note:  You must have Manage Lists permission on a list to create or modify an enforced relationship.

A lookup column that enforces a relationship must also have an index. When you create a lookup column that enforces a relationship, you might be prompted to create the index, and it is automatically created when you click OK. Once a lookup column that enforces a relationship has an index, you cannot remove the index from that column, unless you first remove the enforced relationship.

A lookup column with an unenforced relationship

In a lookup column with an unenforced relationship, from the source list, you can lookup single or multiple values in the target list. Also, when you delete an item in the source list, as long as you have delete permission on the list, there are no additional delete restrictions or delete operations that occur in the target list.

Viewing and editing items in list relationships

One of the beneficial effects of creating list relationships is that you can display and edit columns from two or more lists, on one page. When you create a lookup column in a source list, the primary column displays values from the target list column, but you can also specify that additional, secondary columns from the target list also display alongside the primary column and the other source list columns.

The primary column value is a link that you can click to the display all the column values of that item in the target list. Each secondary column value from the target list displays as text in the source list column. If you create a lookup column with multiple values, each column from the target list, whether primary or secondary, display their multiple corresponding values delimited by semicolons. You can manipulate these primary and secondary columns in list view in much the same way as the columns from the source list, by, for example, adding, removing, filtering, and sorting them.

The primary column name is a name you provide when you create the lookup column. By default, each secondary column name follows a standard naming convention of <primary column name in source list>: <column name in target list>. Changing the name of the primary column, therefore, changes the names of all secondary columns. However, you can modify the default secondary column names and descriptions to make the names more meaningful to you.

Lookup columns with multiple values

1. A primary lookup column with multiple values.

2. A secondary lookup column with multiple values.

3. The column name has been changed from the default name of GetID: Title to Instruments.

Managing list relationships when lists have many items

When you create list relationships by using lookup columns, unique columns, and lists with enforced relationships, you can reach a resource threshold or limit and may be blocked under the following circumstances:

  • If you make a column unique in an existing list that has more items than the List View Threshold (but note that adding one item to a list that makes the list greater than the List View Threshold is an operation that is not usually blocked).
  • If you turn on Cascade Delete or Restrict Delete for a lookup field in a list that has more items than the List View Threshold.
  • You have exceeded the List View Lookup Threshold, which by default is twelve lookup columns.
  • The number of items in a delete operation exceeds 1,000.

See Manage lists and libraries with many items for more information on managing lists with many items.

How to: Create or modify a unique column

Warning: You cannot create a unique column in a list with item-level permissions defined, you cannot break inheritance of permissions from a list to a list item if the list contains a unique column, you cannot create a unique column or modify a column to be unique in a list that contains items with unique permissions or one that has content approval enabled, and you cannot copy a file to a library by using the Open with Explorer command, if that library contains a unique column.

  1. Find the site containing the list.
  1. Select the list name on the Quick Launch, or on the Settings menu, press View All Site Content, and then under the list section, choose the name of the list.
  1. Press List > Create Column.
  1. In the Columns Name box, enter a name for the column.
  1. Under The type of information in this column is, select one of the following:
  • Single line of text
  • Multiple lines of text
  • Choice
  • Number
  • Currency
  • Date and Time
  • Yes/No
  • Lookup (single value)
  • Person or Group (single value)
  1. In the Additional Column Settings section, under Enforce unique values, press Yes.

If you select a data type that is not supported, or of you are creating a lookup column that allows multiple values, the Enforce unique values is not displayed.

  • You cannot remove the index of a unique column. If you want to remove the index, you must change the column from a unique column to one that allows duplicate values first, and then remove the index.
  • If you try to change an existing column from duplicate values to unique values, you may be prevented from doing this operation if the existing column contains duplicate values. In this case, you must remove all the duplicate values first, and then change the column from allowing duplicate values to a unique column.
  • If you want to change the data type of the unique column, you must do the following: change the column to allow duplicate values, remove the index, change the column to the other supported data type, and then make the column unique.

How to: Create a lookup column

These steps walk you through setting up a look up column that displays the primary column, and optionally a secondary column into a list. The primary column can be indexed, but since the secondary column (or field) is controlled by the primary, it can’t be unique or indexed.

  1. Visit the site including the list.
  1. Choose the relevant list name on the Quick Launch, or on the Settings menu, press View All Site Content, and then below the list section, pick the name of the list.
  1. Select List, and then pick Create Column.
  1. In the Columns Name box, enter a name for the column.
  1. Under The type of information in this column is, choose Lookup.
  1. In the Get information from box, choose a target list.
  1. In the In this column box, pick a primary column.
  1. Under Add a column to show each of these additional fields, for each secondary column you want to add, choose the check box beside the column name. Only columns with supported data types are listed.
  1. Further down the line, if you want to create or delete secondary columns, edit the lookup column and decide the selections you want.

Note: You cannot index a secondary column nor can you make a secondary column unique.

  1. If you prefer the lookup columns to be presented in the default view, click Add to default view.
  1. To produce an enforced relationship, choose Enforce relationship behaviour and then select Restrict Delete or Cascade Delete.
  1. Press OK.

How to: Create relationships in the display and edit forms

Once you make a lookup column, you can alter the view and change forms of the target list to guarantee that associated data from the source list is shown. For example, you can modify the presentation or alter page for the Orders list to insert the related Items list and automatically link the two corresponding List View Web Parts together by the passing the Lookup column value.

  1. In the target list (for example, Orders) select the List tab, press Form Web Parts in the Customise List section, and then choose Default Display Form or Default Edit Form.
  1. In the Main Web Part Zone, press Add Web Part.
  1. Select Insert, press Related List, and then pick the related list (for example, Items).

Note:  Only lists for which you have View Items permission are displayed.

The two lists are intuitively connected. After you save the page, when you indicate or modify an item from the Orders list, the related items in the Items list also display.

Leave a Reply

%d bloggers like this: