Cascading look ups in Microsoft PowerApps

In this post, I’ll show you how to create a cascading dropdown that posts back to lookup fields in SharePoint.

Backend Configuration

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

Desired Functionality

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

The Issue

You cannot apply the functions to accomplish the cascading functionality directly with the SharePoint Lookup control.

The Work-Around

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:Distinct(Projects,Title)

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:Distinct(Filter(Projects,Title = PrjNumb.Selected.Value),Project_x0020_Name)

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”,
Id: Value(LookUp(Projects,Title=PrjNumb.Selected.Value,ID)),
Value: PrjNumb.Selected.Value
}

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:{‘@odata.type’ : “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”,Id: Value(LookUp(Projects,Project_x0020_Name = PrjName.Selected.Value,ID)),Value: PrjName.Selected.Value}

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.