Create list relationships by using lookup columns (2023)

In alist relationship, information from one list (thesourcelist) is automatically shown in another list (thetarget list). List relationships let you join information from two lists and keep it consistent while people edit and delete list items.

Relationships help track and maintain information across multiple lists. Target list items can also link to and display multiple columns worth of information from the source list, and relationships can change what happens when an item is deleted (check out Deleting items with a relationship).

In this article

Getting started with list relationships

Unique columns

Adding a Lookup column to an existing list

Creating columns in the source list

Deleting items with a relationship

Managing list relationships when lists have many items

Getting started withlist relationships

To make a list relationship, you will need two lists: the source list and the target list. You should make the source list first.

In this example, we'll use a list of college courses as the source list, and a Reading list as the target list. The goal is to display a course name and course ID for every book on the Reading list without having to manually enter the information.

1. Make the source list. This list will have columns for all the information you will display on the target list later, as well as additional information for each list item for our records. For example, we will make a Course list with the columns: Course Name (title), Course ID (single line text), Class Time (date and time), and Semester Length (choice, with two options: full, half).

2. Fill out the source list. Make list items for all the information you will display on the target list later. For the example, we will add three courses: Intermediate Computer Science, Intro to Computer Science, and Machine Learning. Your list should look like this:

   Course list

   Course Name (Title)	Course ID (Single line text)	Class Time (Date and Time)	Semester Length (Choice)
   Intermediate Computer Science	CS200	9/9/2021 12:00 AM	Full
   Intro to Computer Science	CS101	9/8/2021 9:00 AM	Half
   Machine Learning	CS405	9/10/2021 1:30 PM	Full

3. Make the target list. Let's add an Author column (single line text) and a Cost column (currency).

4. To add a Lookup column to the targetlist, selectAdd Column>Lookup.

5. Enter a name to display at the top of the column. Let's call it Course Name.

   (Video) How to Use a Lookup Field Column in SharePoint Lists | Microsoft SharePoint | 2022 Tutorial

6. UnderSelect list as a source, select the source list. For this example, select the Course list.

7. UnderSelect a column from the list above, select the column from the source list you want to display on this list (the target list) in the new column you are creating. For this example, select Titlesince we want to display the Course Name.

8. To display more lookup information from the source list on the target list, select More options. For this example, select only Course ID.

9. Select Saveand fill out the target list. When you add a book to the targetlist (Reading list),there is now an option to add information from the source list. For the example, for The Future of Computingby Lovelace, select the Course Name "Machine Learning" and the Course ID will be automatically filled in (in this case, it's CS405). Since the columns are lookup columns, you only need to fill out one and the other will fill with the information from the source list.

   Your target list should look something like this:

   Reading list

   Title	Author (Single line text)	Cost (Currency)	Course Name (Lookup)	Course ID (Lookup)
   Computer Science for Humans	Smith	$20.21	Intro to Computer Science	CS101
   The Future of Computing	Lovelace	$18.15	Machine Learning	CS405
   How to Use a Computer Like a Pro	Cline	$44.99	Intermediate Computer Science	CS200
   Typing for Tomorrow	Lorenz	$60.20	Machine Learning	CS405

Now the Reading list displays the Course Name and Course ID information directly from the Course list, and it's easier to categorize new list items by selecting options from the Course list. You can also update the Course list and it will automatically update on the Reading list, as well as link directly to the Course list from the Reading list.

Unique columns

When you create a column, you can specify that the column must contain unique values. This means that the list can't have any duplicate values in that column. You can't add a list item that contains a duplicate value, modify an existing list item that would result in creating a duplicate value, or restore an item from the Recycle Bin if it would result in a duplicate value. And if you create a lookup column in a source list, and you define that column to be unique, the column in the target list also must not contain duplicate values.

Note:Uniqueness is not case-sensitive. 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 prompted to create the index. The index will be automatically created when you select OK. Once a unique column has an index, you cannot remove the index from that columnunless you first redefine the column to allow duplicate values.

Supported column types for unique columns:

• Single line of text

• Choice (single value)

• Number

• Currency

  (Video) Create Lookup Columns in Microsoft SharePoint

• Date and Time

• Lookup (single value)

• Person (single value)

Unsupported column types for unique columns:

• Multiple lines of text

• Choice (multi-valued)

• Calculated

• Hyperlink or Picture

• Custom columns

• Lookup (multi-valued)

• Person (multi-valued)

• Yes/No

Adding a Lookup column to an existing list

Keep in mind that thesource listis the list with information that you want to display, and thetarget list is the list where you want the information to be displayed. Check out the example above in Getting started with list relationships for an example.

1. In the list where you want the Lookup column, selectAdd column>Lookup.

2. UnderSelect list as a source, select the source list to get information from.

3. UnderSelect a column from the list above, select what information you want to display from the source list in this new column in the target list. The information you select will display in the new column you are creating.

   (Video) How to connect SharePoint lists and libraries via a Lookup Column

4. To display more lookup informationfrom the source list in more columns, select More options. All the checkboxes you select here will create new columns on your target list. You can add more columns in these settings later if there's more information from the source list you want to display.

5. If you want deletions from the source list to impact this list, below Deletions from source list affect this list,toggleYes. Check outDeleting items with a relationshipfor more information.

6. Toggle any other settings you would like, then selectSave.

Creating columns in the source list

The following table summarizes the column types that can and can't be used to create lookup columns.

Supported column types:

• Single line of text

• Number

• Date and Time

• Lookup (single value)

Unsupported column types:

• Multiple lines of text

• Choice

• Calculated

• Hyperlink or Picture

• Custom Columns

• Lookup (multi-valued)

  (Video) PowerApps Dataverse Lookup columns and Relationships - Create, Edit, View

• Person

• Yes/No

• Currency

Deleting items with a relationship

When creating a lookup column, underMore options you can toggle whether deletions from the source list affect the target list. If you do, there are two further settings you select from.

Note:You must have Manage Lists permission on a list to create or modify delete relationships.

Source items cannot be deletedmeans that the because the source list is linking with this target list, no items from the source list that appear on the target list can be deleted. You can continue to add to the source list as normal, but you will not be able to delete items like you could previously.

Deleting source items deletes values in this listensures all related items are deleted from both lists at the same time. If you delete from the source list, the item is also removed from the target list.

If you select neither, and instead toggle No, then deletions from the source list do not impact the target list in any way. There are no additional delete restrictions or delete operations.

Managing list relationships when lists have many items

When a list has thousands of items, it may take longer to sort, search, and filter. Lists will automatically index columns in order of creation date to optimize sorting in large lists. To manually index a column, check outAdd an index to a list or library column.

When you create list relationships in a large list, 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.

For more info aboutmanaging lists with many items, check outManage lists and libraries with many items.