In this tutorial, we’re going to talk about how cascading dropdowns work.
Cascading dropdowns are two or more dropdowns that are dependent on each other.
When used in Power Apps, cascading dropdowns can enhance your end user’s experience especially when it comes to filtering huge amounts of data.
Adding A Customer Lookup Table
Let’s start by adding a dropdown on a new screen.
We’re going to add a new screen by duplicating the Master Screen, a technique that we discussed in a past tutorial. We’ll call this screen Item Lookup.
Then, let’s add a data table to the screen.
We need to add a data source for our table to start showing the records we need. Let’s add Table1 as the data source through the Properties pane on the right.
Then, let’s add the fields we need by clicking on “Edit fields”. For this table, we’re going to use the FirstName, LastName, Location and VIP level.
Now that our table contains the records we need, this will serve as our customer lookup table.
Now, the goal here is for us to be able to filter the records here in this table based on the Location and VIP Level. That’s where our dropdowns come in.
Adding The First Dropdown
Let’s go to Input under the Insert ribbon and click on Dropdown.
By default, dropdowns will have the values 1, 2 and 3.
We can change the items in the dropdown by going to the right pane and connecting the data source we used for our customer lookup table, which is Table1.
Then, under Value, we’re going to choose the column that we want to use in the dropdown. In this case, let’s use Location.
Once that’s done, we’ll see all the possible locations in the dropdown. However, we also see a number of duplicates.
That’s because at the moment, it’s merely taking all the records in the Location column regardless of how many times a record shows up. To get rid of these duplicates, we’re going to use the Distinct function.
The Distinct function evaluates records in a specific column and removes any duplicates.
So let’s highlight the dropdown and choose the Items property. Then, we’re going to use the Distinct function and reference Table1 and Location.
Once we lock that formula in, our dropdown now only has unique items without any duplicates.
To help us make the records in this dropdown easier to browse through, let’s sort the items so that they appear in alphabetical order. We’re going to use the function SortByColumn, which usually requires a source. In this example, the source would be our list of distinct items in the Location column.
It also requires a column. In this case, we want the function to sort the items from the result itself. Basically, this result comes from the source that we just referenced, which is our list of distinct items. So we’ll reference Result.
Now, we have all of our unique locations sorted in this dropdown.
Connecting The Data Table To The Dropdown
The next step would be to make this table dependent on the dropdown. For this, we’ll use the Filter function.
So let’s highlight the data table and go to the Items property and use the Filter function. The Filter function usually takes a source and a logical test. In this case, our source is Table1. As for the logical test, we want it to be equivalent to whatever value the user chooses from the dropdown.
If we take a look at the left pane, we’ll see that the dropdown we want to reference here is Dropdown4.
So that’s the element we’ll use for our logical test. We’ll use Dropdown4.Selected.Result because we only want to show the results based on the item selected.
Now, since we have Australia selected in the dropdown, the table is only showing records that have Australia as the location.
Adding A Second Dropdown
Since we ultimately want to cascade our dropdowns, this means that we need at least two dropdowns or more. So let’s add a second dropdown. This time, it’s going to be used to filter our records based on VIP Level.
In this example, the new dropdown is Dropdown5.
Once again, we’ll need to tap a data source, which is Table1. Then, we’ll use VIP Level as the Value.
Since the items in the VIP Level column will most likely have duplicates as well, we will once again use the Distinct function, referencing Table1 and VIPLevel in the process.
This ensures that we have no duplicates in our dropdown.
Now, let’s put those items in order as well. Just like our first dropdown, we’re going to use SortByColumn, this time referencing VIPLevel instead of Location.
With that formula in place, the items in our dropdown are now in proper order.
Next, we need to go back to our data table to make sure that we’re not just filtering based on the first dropdown, but based on the second one as well. The good things about data tables and the Filter function is that we can add in as many filtering criteria as we want.
So if we go to the original formula, we just need to add VIPLevel = Dropdown5.Selected.Result.
Once we’ve saved that formula, we can now choose items from both dropdowns and our data table will filter records based on both of them. For example, if we choose Singapore from the Location dropdown and 1 on the VIP Level dropdown, we’ll end up with two entries matching those parameters.
We can change the VIP Level to 4, and it will show one result matching our filters.
Now, notice how the table comes up empty when we choose VIP Level 5 with Singapore as the location.
This means that there is nobody in Singapore that belongs under VIP Level 5. Although this is understandable, what we want is for 5 not to show up in the second dropdown if there is no record matching it. After all, that’s what cascading dropdowns are all about.
Creating Cascading Dropdowns
At the moment, the Items property for our second dropdown is looking at Table1, takes the distinct items from the VIPLevel column, and sorts them using the actual result.
What we want to happen is for the data source to be narrowed down to whatever is chosen in the first dropdown instead of looking at the entire Table1. For example, if the user chooses Singapore from the Location dropdown, then the VIPLevel dropdown should only display items matching Singapore, and not the entire table.
So we’re going to add the Filter function here, making sure that Table1 is filtered based on the condition that Location should be equivalent to Dropdown4.Selected.Result.
If we check on our dropdown now, we’ll see that the second dropdown does not show 5 anymore with Singapore chosen on the first dropdown.
If we check Canada this time, it shows that there is no 3 under VIP Level.
If we double-check this against our original data source, which is Table1, we’ll see that there really are no matches for VIPLevel 3 in Canada. This means that our formula works.
***** Related Links *****
Power Apps Introduction: Definition, Features, Functions And Importance
Power Apps Environments: Setting Up The App Elements Properly
PowerApps Functions and Formulas | An Introduction
Conclusion
Cascading dropdowns can definitely enhance the end user’s experience, especially if you’re dealing with large amounts of data. This makes it easier for your end users to find the records they need faster, simply because they won’t have to deal with extra items that don’t yield anything as they filter down the results.
Note that you can drop other dropdowns as needed. You don’t have to limit yourself to just two dropdowns. This means that if you have more fields in your data source, you can add more dropdowns depending on how precise you want your filtering to become.
All the best,
Henry
