Thursday, January 24, 2019

A no-nonsense solution to implement cascading dropdowns using PowerApps as a custom form in SharePoint

Search for “powerapps cascading dropdowns sharepoint” on the interwebs and you will find a bunch of ways to get this to work, but I dare to say that the solution I propose is by far the easiest and most elegant. And a big thank you to my colleague Thomas who helped along with this one.

My sample is super easy. I have three lists, Countries, Cities and Customers. Cities has a lookup to Countries, and Customers has a lookup to both Countries and Cities.

image

By default the input form looks like the image below, where all cities are shown regardless of the selected country. But this is easily fixed.

image

Step 1

Click the “Customize” button in the above image to create a custom form using PowerApps.

Step 2

As I want to filter on the City lookup column, I need to add a reference to my SharePoint Cities list.

SNAGHTML10b80a10

and I end up with

image

Step 3

Click the “Customize” button in the above image to create a custom form using PowerApps, and rename the drop down controls to something easier to work with. In my case CountrySelector and CitySelector.

image

You also need to unlock the data card where you will filter the items. As I want to limit what cities are show, I unlock City_DataCard1.

image

Step 4

image

Select the City dropdown control (CitySelector) and change the default Items filter from

Choices(Customers.City)

to

ForAll(
    Filter(
            Cities, // SharePoint list with all cities
            Country.Id = CountrySelector.Selected.Id // Pick cities where country matches the selected one
        )
    ,
    {
        Value: Title, // Create a new item with Value/Id, which will save correct
        Id: ID // It's important to have Value as the first field!
    }
)

Step 5

Save, publish and enjoy!

Explaining Step 4

What happens in the the formula in step 4 is that we retrieve all cities matching the selected country. For each of the returned items, we create a list of new items having the properties Value and Id. The casing and order is important here, as PowerApps cannot resolve the object to allow the Display field to be Value explicitly, but it will implicitly pick the first field.

When you click save, the Id/Value pairs matches just fine with SharePoint to set the correct lookup id.

5 comments:

  1. You've skipped some steps in the process and also there's an error thrown when entering the code in step 4, which would be nice to know what the workaround is for that or how to get rid of it. Otherwise, I think it would have been great.

    ReplyDelete
    Replies
    1. Could you elaborate on the skipped parts and what error you get? And sure, I skipped the list creation and columns as those are mere samples. You should be able to create your lookups and adapt the steps to your lists with little effort.

      Delete
  2. Thanks for the post. I get the following error for Country.Id (Step 4):
    "Delegation warning. This part "Filter" of this formula might not work correctly on large data sets. The data source might not be able to process the formula and might return an incomplete data set. Your application might not return correct results or behave correctly if the data set is incomplete"

    ReplyDelete
    Replies
    1. Which is because PowerApps cannot delegate the query, and will pull in all items (max settings for data source). As long as the number of items are controllable it works.

      Delete
  3. I couldn't get this to work. The CitySelector lookup doesn't filter and contains no items.

    ReplyDelete