Create list relationships by using lookup columns

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 with list 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 target list, select Add Column > Lookup.

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

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

7. Under Select 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're creating. For this example, select Title since 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 Save and fill out the target list. When you add a book to the target list (Reading list), there is now an option to add information from the source list. For the example, for The Future of Computing by 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.

A unique column must also have an index. When you create a unique column, you might be prompted to create the index. The index will be automatically created when you select OK. Once a unique column has an index, you can't remove the index from that column unless you first redefine the column to allow duplicate values.

Supported column types for unique columns:

• Single line of text

• Choice (single value)

• Number

• Currency

• 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 the source listis the list with information that you want to display, and the target list is the list where you want the information to be displayed. For an example, check out the section above, Getting started with list relationships. 

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

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

3. Under Select 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.

4. To display more lookup information from 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, toggle Yes. Check out Deleting items with a relationship for more information.

6. Toggle any other settings you would like, then select Save. 

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)

• Person

• Yes/No

• Currency

Deleting items with a relationship

When creating a lookup column, under More 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. 

Source items cannot be deleted means 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 list ensures 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 don't 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 might 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 out Add 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 about managing lists with many items, check out Manage large lists and libraries.