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.
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.
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.
and I end up with
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.
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.
Step 4
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.
