How to connect SharePoint lists from different sites and configure cascading lookups

In this article:

• What is a SharePoint list?
• How to connect SharePoint lists using a Lookup column?
• How to create cascading dropdowns using Power Apps?
• How to synchronize SharePoint lists between sites?
• Plumsail's alternative for cross-site connections and cascading lookups
• Try Plumsail's Forms alternative

What is a SharePoint list?

SharePoint List is a container that consists of all necessary data in a structured manner. It is a collection of data items called list items. Lists help manage and track information like events, survey results, announcements, etc.

In SharePoint, a list contains columns and rows where columns define the metadata, and rows represent the data. Lists in SharePoint can be easily added, edited, deleted, and viewed.

How to connect SharePoint lists using a Lookup column?

A lookup field in SharePoint is used to connect two or more lists. It is a column type that displays the values of another list. A lookup field helps to avoid data duplication and enhance data accuracy by pulling the data from one list while the other list refers to it.

Following is the method to connect SharePoint lists with a lookup field:

1. Create a list that is going to be used as a source for the lookup field, for example, the Countries list:

2. Create a list to connect to the first list where the lookup field will be located, for example, the Offices list:

3. Click on the "Add column" option and select the "Lookup" column type:

4. Give it a name and select source list and field:

Now, the lists are connected and you can connect every item in the second list to the first list:

There are some limitations to the lookups fields:

• Not all fields can be used as data source for the lookup field, for example, Multiline text, Yes/No fields, Metadata fields and other Lookup fields are not supported.
• No customization or filtering available on default form, custom forms need to be created with PowerApps or other third-party apps to customize the lookup field.
• Lookup fields cannot be used to connect lists on different sites.

Further in the article, we'll take a look at how to work around some of the limitations.

How to create cascading dropdowns using Power Apps?

The method described above works if you want to connect two lists together, but what about more complex connections? What if you need to connect more than two lists, and establish some sort of hierarchy between them?

For example, let's say that you have three lists: Countries, Offices and Clients lists. We'll use the Countries and Offices lists from the example above.

Now, when you design Clients list, you will want each client to be associated with a specific country branch of your company and be connected to the office where they've placed their order, but if you simply add two lookup fields, one to the Countries and one to the Offices list, you'll have way too many options in the Offices lookup field as it will show all the offices that your company has:

To limit the number of options in the Offices lookup, you need to customize the form for that list, and to add a filter to the Offices lookup field which will only show offices that are associated with the selected country. For example, this can be done in Power Apps. If you haven't tried it yet, you can read Microsoft's article on how to Customize a SharePoint list or library form by using Power Apps for additional information.

To start customizing a form, go to List settings → Form settings → Customize in PowerApps:

You will need to add a new data source to the form. Find a SharePoint connector for this:

Add the Offices list as another data source to the form; this will allow using its information for filtering:

To configure cascading lookups in Power Apps, select the Office card, and under the Advanced tab, select "Unlock to change properties" (might be worded slightly differently in your version):

Select the DataCardValue5 (Combo Box) in the Country_DataCard1 (Card), and select the Items function value. Use the Filter function and the in operator you can limit values available for selection in Offices lookup:

Filter(Choices([@Clients].Office),Value in Filter(Offices,DataCardValue4.Selected.Value=Country.Value).Title)

This way, only Offices available in the selected Country are available to choose from:

We also recommend watching this tutorial How to Create Cascading (Dependent) Dropdowns in Power Apps from Reza Dorrani, a principal program manager and content creator at Microsoft:  

With PowerApps, additional lookup fields can be added if necessary. For example, you can have three or more lookup fields filtered in sequence on one form. Still, while this allows you to work around the limitation filtering results and even allows some adjustments of results presentation, it doesn't allow you to connect your lookup to another site.

How to synchronize SharePoint lists between sites?

While the above solutions work for connecting two or more lists which are stored on the same site, what can be done if your lists are stored on different sites, but you still need to connect them in one way or another? For example, let's say you want to keep client's information on one site and all the company information on another site?

Unfortunately, there's no out-of-the-box solution for connecting lists between sites in SharePoint. The best thing you can do in such a case with the available tools is to use Power Automate and synchronize lists on different sites.

Let's say you have a main site, where you keep all the information regularly updated, and you have many other sites, which also need to access the same information, and changes on the other sites should also change the information on the main site.

For this, you can configure Power Automate flows, which will sync lists between sites. One flow will need to run when an item is created or modified, and copy said item to the list on the other site. You can check if an item with the same ID already exists and update it, if it does, or create a new item if it doesn't exist yet:

I've used the following condition to check if an item exists or not:

You can watch the video explanation from Enea Liçaj, a Power Platform business consultant, on such a flow that syncs two SharePoint lists:  

You'll also need to run a flow that will run on delete and will delete the corresponding item on the other site:

Repeat flows for both lists (the one on the main site and the one on the second site), so you'll have four flows in total and your lists will be synchronized. You can then configure lookups to the list on the same site, while also being able to update the list on the other site and have the lists synchronized.

While this approach is not perfect, it does allow to connect lists between different sites, at least in a way. There are no better alternatives out of the box, if the lists need to be connected via lookup. There are third party solutions on the market which allow to do more and do it easier, we'll describe our solution below.

Plumsail's alternative for cross-site connections and cascading lookups

While there is no official way to connect lists between different sites, there are solutions on the market that allow you to do just that. One of these solutions is our Plumsail Forms for SharePoint product, which we want to highlight as a possible alternative.

Plumsail Forms product allows you to customize SharePoint list forms achieving results similar to Power Apps, but with more flexibility and options. One key difference is the ability to add Lookup control to any form, which you can then connect to any site on your domain:

The Lookup control functions essentially the same way as a SharePoint lookup field, pulling data from the selected list, it's highly customizable in both its presentation and functionality - supporting item view customization, searching, and filtering:

On top of that, you can also configure cascading lookups right in the editor without a single line of code, and the results will be filtered based on the selected field, for example, you can make Office control depend on the Country selected with just a few button clicks:

Lookup control can be used in conjunction with List or Library control, which allows you to present any list or library directly on the customized form, and even if this SharePoint list or document library is located on another site, it can still be attached to the parent list via the lookup control, for example, your clients list can be located on a separate site, and still be connected to a specific Office in the Offices List:

Try Plumsail's Forms alternative

By taking advantage of Plumsail Forms for SharePoint, you can streamline cross-site connections without the need to synchronize lists between sites.

If you'd like to try it out and see if it works for your needs, get started with 30 days free trial of Plumsail Forms for SharePoint Online by following the installation instruction in our documentation, which has a video walkthrough.

If you're using SharePoint 2019 or Subscription Edition, follow the SharePoint On-Premises installation guide.