Cascading Dropdowns in PowerApps SharePoint Lookups
In this post, I’ll show you how to create a cascading dropdown that posts back to lookup fields in SharePoint.
I have two lists in SharePoint Online:
1) Projects – This list holds project information such as the Project #, Project Name, Description, etc. The list has multiple Project #’s. A single Project # can be associated with multiple Project Names.
|Figure 1 – Projects List Configuration|
2) Project Hours – This list has two lookup fields: One that looks up the Project # from the Projects list and another that looks up the Project Names from the Projects list.
|Figure 2 – ProjectHours List Configuration|
When the user creates a new item in the Project Hours list, we want them to select a distinct Project # and have that filter out the list of Project Name’s to show only Project Names associated with the selected Project Number.
|Figure 3 – Desired Outcome of Cascading Dropdowns|
You cannot apply the functions to accomplish the cascading functionality directly with the SharePoint Lookup control.
We will need to add two separate Drop Down Controls in the app and apply our cascading function to those controls. We can then set the Default Value of our SharePoint Lookups to the values of our Drop Down Controls so that they are written back to SharePoint.
Here are the Steps to Achieve this:
**I’m going to glance over the basics of creating the PowerApp itself and highlight how to do the cascading piece.
1. Create a Blank PowerApp
2. Add a Data Source to the Projects and Project Hours Lists
3. Add an Edit Form to your PowerApp and include the Project # and Project Name fields
4. Insert two Dropdown Controls – One called PrjNumb and another called PrjName
5. Change the “Items” property of the PrjNumb Dropdown to the following:
The Distinct function ensures that we only receive unique Project #’s. This is important because in our Projects lists we have multiple records with the same Project # (See Figure 1). If we did not have the Distinct function then we would see the same Project # value listed twice. For the Distinct function, you need to pass in the name of your Data Source first followed by the Field in your Data Source that you want to return.
|Figure 4 – Project # Drop Down Formula|
6. Change the “Items” property of the PrjName DropDown to:
In this function we are using the Distinct function again to ensure we get unique values. Within the Distinct function we are applying a Filter function. Within the Filter we are passing in the name of the Data Source followed by our filter function which is where the Project # (which is the Title field in that list) is equal to our selected value in our Project Number Drop Down field in our PowerApp. We are then telling it that we want this Drop Down control to return the Project Name’s as our value options.
|Figure 5 – Project Name Filtered Drop Down|
7. Change the “Default” property of your SharePoint Project Number Lookup field to:
‘@odata.type’ : “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”,
To set the default value of a SharePoint Lookup column, you have to tell it the ID of item in the list as well as the text value. The text value in this case is just the selected value of our PrjNumb Drop Down field. To get the ID of that, we have to use a LookUp function. In that we will pass in the name of our Data Source, followed by a function that says where the Title (Project # value) is equal to our selected Project Number in our form. We then tell is to return the ID field value.
|Figure 6 – Project # SharePoint Lookup Default Value Configuration|
8. Change the “Default” property of your SharePoint Project Name Lookup field to:
|Figure 7 – Project Name SharePoint Lookup Default Value Configuration|
That’s all there is to it. You now have cascading dropdowns that will update your SharePoint Lookup Columns.
Great explanation where so many others fall short by not offering a complete series of steps!
One question: When editing an existing item in the SharePoint list, my custom dropdowns default to an initial value instead of displaying the saved values for the item. How can I fix this?
I’ve followed your instructions to a tee. However on steps 7 and 8 when I change the Default property value, I get this error on both Project Name and Project Nbr dropdowns. “powerapps error “expected text or number. we expect text or number at this point it the formula. This error will occur if you use a function that requires either text or a number but you supply, for example, a boolean (true/false) value”. Any thoughts as to what I may have done wrong?
your post is really helpful.
I am still struggling to populate the SharePoint drop-downs with the added extra drop-downs.
I had the problem Beth mentioned but could solve them with manually retyping the Default property. (the error vanished after IntelliSense recognized “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference” and highlighted it in red)
I also needed to add “.Value” after the Title:
Distinct(Filter(Projects,Title.Value = PrjNumb.Selected.Value),Project_x0020_Name), otherwise i was getting a red underlined “=” with an “invalid argument type” error.
Any help how to populate the SharePoint fields or how to further debug the solution will be greatly appreciated.
Thanks and BR,
You nailed it. Thank you