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.

Sign up for exclusive updates, tips, and strategies

    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, LookUpColumnNameOfTheChildList.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 Clark

    As the Principal Solutions Architect at Mr. SharePoint, I help companies of all sizes better leverage Modern Workplace and Digital Process Automation investments. I am also a Microsoft Most Valued Professional (MVP) for Office Apps & Services.

    Subscribe
    Notify of
    guest
    6 Comments
    Oldest
    Newest Most Voted
    Inline Feedbacks
    View all comments

    Mike Hobson
    Mike Hobson
    7 months ago

    Following the steps exactly, I got an error on the child dropdown saying “Expected Operator. We expect and operator such as +, *, or & at this point in the formula.” In reviewing your screenshots above, you had the exact same error and red “X” showing on the dropdown. How to fix?

    Nirupama
    Nirupama
    Reply to  Mike Hobson
    4 months ago

    Hi, I hope you found the solution. I too faced the same issue. It worked after I modified the formula to this:
    Filter(NameOfChildList, LookUpColumnNameOfTheChildList.Value=NameOfParentDropDown.Selected.Title)

    Aisling Walsh
    Aisling Walsh
    Reply to  Nirupama
    2 months ago

    Thank you, your comment here helped me fix this!!!

    Jacob
    Jacob
    2 days ago

    I there a way to get the cascading lists to work in a sharepoint list? Ex, when i change the value of one row to Lab, the next column options would change respective to that

    6
    0
    Would love your thoughts, please comment.x
    ()
    x
    Scroll to Top
    Tweet
    Share
    Share
    Pin