PowerApps, SharePoint, cascading menus, and the Collect function

Hi everyone,

As you may have discovered, working with the combination of PowerApps, SharePoint and cascading menus can be confusing, so I’d like to offer one of my examples as a way to help your understanding of them.

Here are the requirements:

• Create a PowerApp that will use SharePoint list as its backend.

• The PowerApp will have three cascading menus that filter based on each other’s selections.

• The PowerApp will accept input from a user and deposit that input as items into a SharePoint list.

Because the requirement was to have a full-blown PowerApp vs. a customized SharePoint list form, we created the PowerApp with the following steps:

• powerapps.microsoft.com

• click on Create >> Start with Data >> then choose your SharePoint site and list

In regards to SharePoint we actually had two lists in total.

(Please pay no attention to the meaningless names of the lists or the names of the content I have created. The main point is how we reached the goal of everything working. )

List 1: Incidents: this served as the main backend to which the PowerApp was based on and which would accept the input from the PowerApp

I would like to call out the three columns LOCATION, DEPARTMENT and CANDY. Notice how they are ‘Single Line of Text’. I did this to make my life easier. I could have tried to create these as Lookup fields that pull values from list, but I didn’t feel like staying up all night trying to get that to work.  SharePoint lookups and PowerApps don’t play well with each other.

List 2: Locations: This list served to hold the values for my three dropdowns

Here is my PowerApp, notice the three dropdowns, Location Department and Candy.

When I select Location, you will notice four options (blank, EganVille Pembroke, Renfrew).

If I select Pembroke, then the ‘Department’ drop-down displays Floral, and ‘Candy’ display Licorice.

Now, if I select Deli from the ‘Department’ menu, ‘Candy’ switches to Tic Tac. So you can see the menus are connected to one another.

How did we accomplish this?

1. I added the Datasource that contains my drop-down menu items (Locations) to the app (View >> Data Sources >> Connectors >> SharePoint >> choose your Account >> input the SharePoint URL >> select your SharePoint list)

2. After I created the PowerApp I removed the Location, Department and Candy fields from the app.

3. I then added Drop Down menus to replace each one of those (Insert >> Control >> Drop Down)

4. For the first Drop Down (Location), I added the following formula to the ‘Item’ property: Sort(Distinct(Locations,Location),Result).

   The Distinct function tells PowerApps to display a unique entry in the drop-down menu in case there are redundant entries in your datasource. ‘Locations’ is the name of my data source, in this case, a SharePoint list. ‘Location’ is the name of the field being queried from SharePoint. Then I encapsulated the entire function with a Sort() function so that the items in the drop-down appear in ABC order.

5. For the second Drop Down (Department), I added the following formula to its ‘Item’ property: Distinct(Filter(Locations, Location=ddLocation.Selected.Result),MyDepartment).Result

   ddLocation is the object name of the Location Drop-down menu. I used dot notation to have PowerApps display the selected result (ddLocation.Selected.Result). I used a Filter function that says ‘Display items from the MyDepartment SharePoint field based on the value selected in the ddLocation menu. Finally, I used the Distinct function once again to display only unique values.

6. For the third drop down, I set its ‘Item’ property to the following Distinct(Filter(Locations, MyDepartment=ddDepartment.Selected.Result),MyCandy).Result

   ddDepartment is the name of the Department drop down in which this third drop down is dependent on. I used dot notation to have PowerApps display the selected result (ddDepartment.Selected.Result). I usded a Filter function that says ‘Display the Candy items based on the value selected in the ddDepartment menu. Once again, I used the Distinct function to display unique values.

The last piece to talk about is how to properly update the SharePoint list with the data that the user chooses from within the PowerApp. Remember, that this PowerApps is sort of stitched together with the values from the dropdowns discussed above being pulled from a secondary list. Therefore, we need to have more control over exactly what list is being updated. I chose to use the Collect() function to have that control.

I placed this code on the OnSelect property of the check icon of the Edit Screen:

Collect(Incidents,{Title: txtTitle.Text, ‘First Name’: txtFirstName.Text, ‘Last Name’: txtLastName.Text, ‘Phone Number’: txtPhoneNumber.Text, Description: txtDescription.Text, Date: dtDate.SelectedDate, Location: ddLocation.Selected.Result, Department: ddDepartment.Selected.Result, Candy: ddCandy.SelectedText.Value});

With this methodology, I was able to map each value from my PowerApps fields to the columns within my SharePoint list. The Collect function takes a datasource parameter, in this example it is the list named ‘Incidents’. It then accepts Column: value pairs, within the curly brackets.

Hopefully this makes sense and helps you on your way enjoying the benefits of  PowerApps and SharePoint.