Create Cascading Drop Down Input Form for SharePoint with PowerApps

My Scenario:

I had the requirement to have a list input form in SharePoint online, that contains “Scope”, “Category” and a “Subcategory”.
Of course – to have it end user friendly – these dropdowns had to be cascading.

First of all: as of today, cascading dropdowns in PowerApps only works smooth with TEXT only values!! (yes, it’s a real mess)
So how to prevent the user from typing mistakes with text only fields?
–> Provide the user a lookup input interface and create the text only values with a simple Flow.

Solution:

First of all create the lists – 3 simple lists with Title only for the lookup values and 2 lists for mapping

In the mapping lists make the Title not required, add 2 lookup columns that are required and 2 additional columns for the text value

Create a simple Flow for each of the two mapping lists, that updates the text columns, that we’ll use later for our dropdown values

Then create a target list (also text only fields for these three “dropdown” columns!) Of course all other columns can be treated as standard.

In the main list click on “Customize Form” under PowerApps menu
Unlock the 3 Data Cards under “Advanced”, replace the text input controls with dropdown controls and rename them to something meaningful like
“ddScope”, “ddCategory” and “ddSubCategory”
set the “UPDATE” property of the data cards to the name of the SP field to be updated with the value

Now modify the “Items” property of the new dropdown fields.

ddScope:
Distinct(cascMap_ScopeCat, LUScopeText)
ddCategory:
Filter(cascMap_ScopeCat, LUScopeText = ddScope.Selected.Result)
ddSubCategory:
Filter(cascMap_CatSub, LUCategoryText = ddCategory.SelectedText.Value)

(if you want to sort the values, you can put a SortByColumns() function around the filter formula)

Now the lookups are already working, but if you click on Save, the values are not written to SharePoint. To fix this, we have to re-add the original columns to the form. When added again, unlock them under Advanced and set the “Default” property to the value of the dropdowns.

Scope:
ddScope.Selected.Result
Category:
ddCategory.SelectedText.Value
SubCategory:
ddSubCategory.SelectedText.Value

Last but not least, hide those fields from the form by setting the “Visible” property to false

Save, publish and enjoy 😉

(No Ratings Yet)