Mikko Koskinen
Power Platform, PowerShell, SharePoint
Cascading Fields in Power Apps Forms – Basics and Few Tips
As far as I remember, customizing SharePoint forms and cascading fields have been one of the main areas that organizations have asked. I have done these customizations for classic and modern SharePoint with InfoPath, custom solutions, and other form solutions. Now we can use Power Apps in many cases, and they’re still is no exception here. Many app creators are wondering how to make these customizations and how can I create cascading fields, for example?
A full walkthrough for custom forms would need a separate series of posts, so I am concentrating on the cascading fields and small form examples here. Of course, with Power Apps, it’s not about SharePoint only, and the following techniques can be used against other data connectors also. Here is my typical way of implementing cascading fields in Power Apps. I’ve used this way against small SharePoint lists and in multifield large business scenarios.
Main Requirements
- We have a SharePoint list that has a Type field
- We want to able to select the type from a range of values that can be categorized into multiple different categories
- We want to give the administrators an easy way to maintain these values, so they should be placed to a SharePoint list
- There are two SharePoint lists Main Category and Sub Category
- Sub Category has a lookup column pointing to the Main Category
 |
 |
In the custom form, the users are first selecting the category, and then they can select the value belonging to this category from the second drop-down box
Basic Form With Cascading Fields
Let’s create a simple canvas app for our form. Form only has a text box for the title, two drop-down boxes for selecting the categories, and a submit button. You also need to connect the app to all the three lists in SharePoint.
Even though this is a simple list, remember the naming convention. Taking a habit to rename the controls and screen will help you in the future. You can also use grouping, as I did for the labels, to make it easier to find important elements in the tree view.
- Add the necessary label, one text input, and two drop-down box
- Select the Items property of the first drop-down (ddpMainCategory in the example)
- Connect the items to the Main Category list
- Make sure the value property is set as Title
- Do the same thing to another drop-down (ddbSubCategory) but connect it to the Sub Category list
The basic setup is now done, but we are missing the cascading part. The category selection doesn’t filter the value list. We need to change the items selected for the second drop-down to make this happen.
In the items selection of the ddpSubCategory, we need to filter the items based on the selection in the other drop-down box
Filter('Sub Category','Main Category'.Value = ddpMainCategory.Selected.Title) After this, we have the basic cascading fields ready
Saving the Details
In the SharePoint list, we only have the Title, and Type Value, and the values are now read from a different SharePoint list. Therefore we didn’t use a Power Apps form element where we have the SubmitForm action available. To save the details from our custom form, we need to use the Patch command. Add the following command to the OnSelect -event of the Save button. Again, remember to comment your code. It helps. I’ll promise
These functions will save the details to SharePoint based on what the user selected from the drop-down and resets the form for the next one. The idea in the Patch command is:
//Save details to SharePoint
Patch('Important List',
Defaults('Important List'),
{
Title: txtTitle.Text,
'Type Value': ddbSubCategory.Selected.Title
}
);
//Reset form element
Reset(txtTitle);
Reset(ddpMainCategory);
Reset(ddbSubCategory);
- First ‘Import List’ is the name of the data source where we are running the patch command
- Defaults command will tell to create a new item in the list
- Next, we are adding the values from the form into the SharePoint columns of the new item
- Value from the second drop-down box can be read from the Selected property
- More info from the Patch – function: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-patch
 |
 |
Cascading Fields – Next Version
You might notice a couple of things in this way of building cascading drop-downs. Updating the ddbSubCategory field might take some time after we change the value in ddpMainCategory, and the first item in the drop-down box is selected automatically. In many situations, we want users to see an empty value in the drop-down and take action to choose the one they need.
- To tackle these issues, we will read the drop-down selection values to collections in the app start
- Open the App OnStart settings and add the following functions to save the selected items from SharePoint to two collection
//Main Categories to collection
ClearCollect(MainCatSelection,"");
Collect(MainCatSelection,'Main Category');
//Sub Categories to collection
ClearCollect(SubCatSelection,"");
Collect(SubCatSelection,'Sub Category');
- First, we are clearing the possible old collections and adding one empty row into them
- Then we collect the SharePoint rows from the lists and save them to the collections
- Next, we need to update the items settings of the drop-downs
- ddpMainCategory = MainCatSelection
- ddbSubCategory = Filter(SubCatSelection, IsBlank(Title) Or ‘Main Category’.Value = ddpMainCategory.Selected.Title)
- In the ddbSubCategory, we are filtering the collection so that the first black item is selected and also all the other rows that have the selected category value in the ‘Main Category’ field
Click the Run OnStart action for the App to populate the collection for the test. Now we have cascading drop-downs that are fast to use and show an empty value before the user is making the necessary actions.
Mikko Koskinen 