Cascading Dropdown Tutorial: SharePoint List and Power Apps

It’s possible to create a cascading dropdown with SharePoint Lists. Unfortunately, it may not be that simple as creating a lookup column or downloading multiple files from SharePoint.

The good news is, it’s completely learnable. Although there are a few codes involved — you will not get lost if you do it step-by-step using this guide.

Note that the steps explained below can only be applied in the modern experience. For the classic experience, you need JavaScript or InfoPath.

Let’s get started.

What is a cascading dropdown?

A cascading dropdown is commonly used in forms and is meant to improve the user experience.

It’s called “cascading” since the selection of the parent dropdown list will affect the child dropdown list and limit the choices to only those related to the parent.

Here’s a good example of a cascading dropdown where selecting the parent item (department) dictates the options in child dropdown lists:

Selecting a department would affect the choices in the teacher and course code lists

In other words, whenever a user selects an option in the parent list, the child list gets filtered according to the option selected.

How to Create a Cascading Dropdown in the SharePoint List

To create a cascading dropdown list, you basically need to create a power app first, connect it to your data in SharePoint, and add the cascading lookup logic.

Here are the steps:

Step 1: Prepare Your SharePoint Data

First off, you need to create separate lists in SharePoint for each dropdown list you want to include in the form.

Let’s continue with the example earlier.

Since there are two dropdowns (department and examination), you need to create a separate list for each, with the child list using a lookup functionality.

The parent dropdown list is “Department” — which contains the different departments as titles:

Department SharePoint List with only three entries

For the child dropdown list, the entries (titles) are the options in this list.

Then, a separate lookup column connects this list with the parent list.

Examination SharePoint list with lookup column

Since you need to add a lookup column, that means you need to edit and link each entry to its correct department.

If you need help with this step, read my guide on how to create a lookup column easily.

Step 2: Create a Canvas App in Power Apps

In this step, you need to use Power Apps.

From your SharePoint site, fire up the app launcher and select Power Apps.

The app launcher can be found on the upper-left corner of the page

On Power Apps, click “Create” from the left bar and create a “Canvas app from blank”.

The create link can be found on the left bar

Then, give the app a name that you can easily recognize and choose the tablet format to see it in that layout.

Click the “Create” button to proceed.

Give the app a name you can easily remember

Step 3: Add Data From SharePoint

Before you can create a working cascading dropdown list, you must connect your data in SharePoint with Power Apps.

To do this, click the data icon on the left bar and click the “Add data” button.

The data icon looks like a large disk

In the choices, go to the “Connectors” tab and select “SharePoint”.

If you got to the Power Apps from the app launcher, you will then see your account and will have to click on it.

Find the option to select SharePoint as the data source

To actually connect your data, you will need to either provide the link to the SharePoint list or simply find the site that has your lists.

Decide if you want to connect SharePoint through a link or through the site explorer

Select the participating lists and click the “Connect” button at the bottom.

Choose the lists to connect with Power Apps

You will then see the lists in the data sources.

Step 4: Create the Form

In this step, you must add two dropdown controls along with their own labels.

On the left bar, click on the add (+) button.

Under the “Input” group, select “Drop down”.

The dropdown option can be found under the input tab

The next step is to customize this dropdown, which shall be called “Department Dropdown”.

Once you click on the dropdown inside the canvas, a properties pane will automatically appear on the right side.

Under the properties (default) tab, change the “Items” to the parent dropdown (in this case, it’s “Department”).

On the “Value” setting, change it to “Title”.

Change the items and value options of the dropdown

To add a label, click the add button once again and select “Text label” under the “Display” group.

The text label can be found under the display group

Now, create another dropdown and text label for the child list.

Don’t forget to change the “Items” and “Value” settings for the dropdown as well.

Add another dropdown and change the items and value settings

Step 5: Add the Cascading Lookup Logic

This stage may be a little confusing since you will need to add a code or a formula to tell the dropdown to filter the options based on what’s selected on the parent dropdown.

Before you start, make sure to click on the child dropdown first.

Then, on the formula bar, enter a filter formula as its “Items” property:

Filter(NameOfChildList,NameOfChildList.Value='NameOfParentDropDown'.Selected.Title)

In the example, the formula looks like this:

Filter(Examination,Examination.Value='Department Dropdown'.Selected.Title)
Formula for the cascading lookup logic

With that, the child dropdown will now show only the options linked with the parent dropdown.

Before you publish the app and use it online, make sure to test the dropdown first and see whether or not it works.

How to Default the Cascading Dropdown to Blank

One of the things you may have noticed is how the dropdowns always default to the first entry.

Unfortunately, there really isn’t a solution as of now right within Power Apps.

However, there is a workaround you can implement that will default the dropdown to blank, which goes away only when the user selects an option on the parent dropdown.

To start, visit your parent list for your cascading dropdown, add a new entry with a blank line (hyphen).

Do the same on the child list and on the lookup column, connect the new entry (blank line) on the parent list.

Add a new entry and provide only a hyphen

Now, go back to Power Apps.

Select the parent dropdown and enter the following formula as its “Items” property:

Sort(NameOfParentList,Title)

In the example, the formula will be:

Sort(Department,Title)

Note that this formula will also sort your parent dropdown (not the child). Since the line goes first alphabetically, the dropdown will default to it.

When a user first visits your form, it will now look like this:

Cascading dropdowns now show a blank line by default

As you can see, the steps to create a cascading dropdown with SharePoint Lists and Power Apps may be a little bit hard.

However, if you have been using Microsoft Excel or you’re familiar with programming, the formula isn’t really that hard to understand.

If you’re still confused or you have some questions, feel free to drop a comment below. You can also reach me through my contact page.

About Ryan

As the Principal Solutions Architect at Mr. SharePoint, I help companies of all sizes better leverage the Modern Workplace and Digital Process Automation investments.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top
Tweet
Share
Share
Pin