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 😉