Create Cascading (Dependent) Dropdowns In Power Apps

Create Cascading (Dependent) Dropdowns In Power Apps

Cascading dropdowns are a set of dropdowns that show values based on the previous selection in a hierarchy. When built well, they help app users quickly navigate an otherwise overwhelming set of options. For example, selecting a car could be done by choosing the manufacturer, model and year in that order. Once the user selects a manufacturer, the next dropdown only shows models for that manufacturer. Similarly, after the model is selected only the years where the model was made would be displayed.

In this article I will show you how to build a set of cascading dropdowns in Power Apps.

Table of Contents:
Introduction: Order A Mobile Phone App
Setup The SharePoint List
Create The Cascading Dropdowns
Populate Cascading Dropdowns With Values From The SharePoint List
Add A Blank Option To Each Cascading Dropdown
Prevent Cascading Dropdown Selections In The Wrong Order
Save Cascading Dropdown Selections To A SharePoint List




Introduction: Order A Mobile Phone App

Employees at a construction company use the Order A Mobile Phone app to choose the phone they want to buy. The app ensures only valid combinations of manufacturer, model and color are selected.




Setup The SharePoint List

Create a new SharePoint list called Devices Catalog to hold all of the possible phone options with the following columns:

  • Manufacturer (single-line text)
  • Model (single-line text)
  • Color(single-line text)

Include this data in the list:

ManufacturerModelColor
AppleiPhone 12Silver
Apple iPhone 12Black
Apple iPhone 11Silver
Apple iPhone 11Pink
SamsungGalaxy S21Red
SamsungGalaxy S21Blue
SamsungKiaRed
SamsungDodgeBlack

Another SharePoint list called Devices Orders will be created later in this example to store which devices were selected and who selected them.




Create The Cascading Dropdowns

Open Power Apps Studio and start a new app from blank. Insert three pairs of labels and dropdowns for Manufacturer, Model and Color as shown below.



Add the Devices Catalog SharePoint list to the app. We will use it to populate the dropdowns with values.




Populate Cascading Dropdowns With Values From The SharePoint List

When an employee picks a value from a dropdown it restricts values in the next dropdown to only valid selections found in the Devices Catalog SharePoint list.



Use this code in the Items property of the Manufacturer dropdown to show a unique list of values. We must use the Distinct function because otherwise each Manufacturer would be repeated several times.

Distinct('Devices Catalog', Manufacturer)



Next, select the Model dropdown…



…and use this code in the Items property to show only valid Models for the chosen Manufacturer.

Distinct(
    Filter(
        'Devices Catalog',
        Manufacturer=drp_Manufacturer.Selected.Result
    ),
    Model
)



Then, select the Color dropdown…



…and write this code in the Items property to display only valid Colors for the chosen Model. Now we have a working set of cascading dropdowns.

Filter('Devices Catalog', Model = drp_Model.Selected.Result)




Add A Blank Option To Each Cascading Dropdown

If we change the Manufacturer dropdown to a different value we immediately notice a problem. The Model and Color dropdowns will show a new value without us making any selection. This is a confusing user experience that we should eliminate.

All dropdowns should show no selected value when the employee opens form and revert to no selection when a parent option changes.. To accomplish this we will add a blank option to the top of each dropdown as shown below.



Update the Items property of the Manufacturer dropdown to this code to add a blank option.

Ungroup(
     Table(
         {Value: Blank()},
         {Value: Distinct('Devices Catalog', Manufacturer)}
     ),
     "Value"
 )



Then change the Items property of the Model dropdown to this code…

Ungroup(
    Table(
        {Value: Blank()},
        {Value: Distinct(
            Filter(
                'Devices Catalog',
                Manufacturer = drp_Manufacturer.Selected.Result
            ),
            Model
        )}
    ),
    "Value"
)



…and finally replace the Items property of the Color dropdown with this code.

Ungroup(
    Table(
        {Value: Blank()},
        {Value: Filter(
            'Devices Catalog', Model = drp_Model.Selected.Result
        )}
    ),
    "Value"
)



Now all of the dropdowns have a blank value at the top of the selection list.




Reset Cascading Dropdowns When A New Selection Is Made

When we select a Manufacturer the Model and Color dropdowns below it should be reset to their default value of blank. Use this code in the OnChange property of the Manufacturer dropdown

Reset(drp_Model);
Reset(drp_Color);



Likewise, when we select a Model the Color dropdown should reset to blank. Write this code in the OnChange property of the Model dropdown

Reset(drp_Color);



Ensure all dropdowns have a Default value of blank.

Blank()




Prevent Cascading Dropdown Selections In The Wrong Order

We don’t want employees to select the Model until the Manufacturer is selected. Nor do we want employees to choose a color until the model is decided. Guiding employees to make choices in the correct order is fundamental to our cascading dropdown design.



We can prevent employees from inputting values by setting a dropdown to disabled mode. Use this code in the DisplayMode property of the Model dropdown to prevent selections before the Manufacturer is chosen.

If(
    IsBlank(drp_Manufacturer.Selected.Result),
    DisplayMode.Disabled,DisplayMode.Edit
)



Similiarly, use this code in the DisplayMode property of the Color dropdown to prevent selections before the model is decided.

If(
    IsBlank(drp_Model.Selected.Result),
    DisplayMode.Disabled,DisplayMode.Edit
)




Save Cascading Dropdown Selections To A SharePoint List

Employee mobile phone orders are saved to another SharePoint list called Device Orders so the I/T department knows what phones to order. Create the Device Orders SharePoint list with single-line text columns for Manufacturer, Model and Color. Unhide the Created By and Created columns so we can see who ordered the phone and when.

Created ByCreatedManufacturerModelColor



Add the Device Orders SharePoint list to the app.



Then insert a new button with the text “Submit” at the bottom of the form…



…and use this code in the OnSelect property to save the mobile phone order to the Device Orders SharePoint list and reset the dropdowns.

// save new record to SharePoint
Patch(
    'Device Orders',
    Defaults('Device Orders'),
    {
        Manufacturer: drp_Manufacturer.Selected.Result,
        Model: drp_Model.Selected.Result,
        Color: drp_Color.Selected.Color
    }
);
// reset dropdowns
Reset(drp_Manufacturer);
Reset(drp_Model);
Reset(drp_Color);



We are now finished making the Order A Mobile Phone app using cascading dropdowns to restrict employee selections to only valid devices.





Questions?

If you have any questions or feedback about Create Cascading (Dependent) Dropdowns In Power Apps please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.

Matthew Devaney

Subscribe
Notify of
guest

13 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Dan
Dan
2 years ago

Matthew, nice technique. We’ve tried using the ootb “Depends on” functionality for drop-downs, and I’ve had some mixed results (though it still basically worked).

What are some of the benefits of using this method over “Depends on”?

For one, we have the introduction of the blank option…but wouldn’t AllowEmptySelection + custom configuration in the Items filter property mirror this?

Ex:

AllowEmptySelection: true
Items: …drp_Boop.Selected.Value=Blank() Or Boop=drp_Boop.Selected.Value,…

Have you found differences in performance, greater flexibility, anything like that? Definitely depends on the situation but would love to hear your thoughts.

Sean Hodkinson
Sean Hodkinson
2 years ago

Hi Matthew, thanks for another great article. I had been using an extra item called “(Select Option)” in my dropdowns. I like the idea of using blank and the ungroup function!

Amod Tiwari
Amod Tiwari
2 years ago

Hi Mathew,

How to set default value of parent drop down in edit form please. i am struggling with this to get selected value as default.

please suggest,

thanks
amod

3rmi M
3rmi M
2 years ago

Thank you Matthew. I have a question on {PATCH}ing
I tried several ways to resolve this and I am still running into problems.

If I have about 10 columns including the columns for [Reporting month] and [Project name]
if each end user wanted to use the previous month data, view it/make few changes then Save-As the new [Reporting month]

can we use patching to create the new [Reporting month] record without {editing} the previous [Reporting month] record?

Thank you

Erol
Erol
2 years ago

How do you add a Sort to the Model and Color Dropdown fields? I have tried a couple of different things and cannot get to the correct syntax. Otherwise I think this is a very helpful post! Please let me know if you can share your thoughts. Thank You!

Martin
Martin
1 year ago

I think I must be missing something. I have a table with 11 columns. I want dependent drop downs.

The first column of the table contains the list of options to go into the first drop down. So far so good. (Let’s say, column 1 is a list of car manufacturers. Column 2 is the model list for the manufacturer at column1 item1. Column 3 is the model list for manufacturer at column1 item 2, and so on.

What I want to do is this: if I select item 1 in column 1, I want the contents of column 2 to be the contents of the second drop down box.

If I select item 2 in column1, I want the contents of column 3 to appear in the second drop down.

As far as I can tell, the only way to do this is to create a table with every combination listed. So instead of my simple 11×10 table, I have to creat a table with 100 rows?

Fabian
Fabian
1 year ago

Thanks Matthew for this (and your work in general). Works like a charm in my form.

I’d like to take it a little further, if it is possible. The blank fields are really nice, when there a multiple choices. But when – further down the form hierarchy – there is only one choice left, in my app the dropdown field needs to be automatically filled. Or rather the blank should only be displayed, when there are more than one choices left. Is there a way to achieve this?

Hope you have some spare time to give me a hint how (or if) this is possible.

Devendra Singh
Devendra Singh
11 months ago

How to make dependent in reverse order as well. If someone select item in dropdown2, it should be visible dependent items in dropdown1.

George
George
8 months ago

Thanks for the tips Matthew. Do you know maximum number of cascading drop downs allowed in Power Apps? I have an excel file with 12 dependent columns that I want to convert in to cascading drop downs. When I get to the 6 column(drop down), I lose the dependency link from the first 5 drop downs. Any suggestions? Thanks.

Andy
Andy
4 months ago

Hi Matthew, Great article, incredibly clear i had one question i had hope to use lookups to populate the initial table Device Catalog style list, could this work or does it need to be text to contain the value and not a lookup reference?