Why?
A few Posts ago (Power Apps: Get Dataverse Option Set ID as well as Label), I showed that working with a Choice Column in Dataverse (previously known as an Option Set in the Common Data Service) could be challenging….
Mainly thanks to all the functionality that exists behind these “complex objects”. Functionalities like multi-language and single-place-management.
What?
In this post I will show how you can use a selected (global) Choice Column value of a record in one table, to filter records in another table that share the same Choice Column. This means we have no direct relation between the tables.
Just think of the scenario where we want to have a Business Applications for organizing events. We can plan an Event on a specific date and hold it on a specific Location. However the Event can be of a specific category and based on that category, a Location is suited. Based on this scenario we can have the following data model:
| Event Table | Location Table |
|---|---|
| Event Name | (one Location can be linked to many Events) |
| Event Date | Location Name |
| Event Category (single select) | Supported Event Category (multi select) |
Below some example data:
In a Canvas App for users managing Events, we want to have a Combobox (ComboBox_Events) on top showing us the future Events. Below this we want to show a Combobox with the Locations that are suited for the selected Event based on the category of the Event.
How?
In the screenshow above we see two comboboxes: on the left ComboBox_LocationsDirect and on the right ComboBox_LocationsVariable. The left ComboBox_LocationsVariable Combobox contains the Items expression:
Filter(Locations,ComboBox_Events.Selected.'Event Category' in 'Supported Event Categories')This can result in an error like: ❌ “This data type is unsupported for evaluation.” ❌ and this will show us no items in the control 😩. I have seen some tenants where this error notification related to “scoping” has been solved. Therefor please be sure to check if this workaround needs to be applied in your case.
1) Use a Contextual Variable to contain the Choice Column value. Do this with an expression on the OnChange property of the ComboBox_Events Combobox:
UpdateContext({varcSelectedEventCategory:Self.Selected.'Event Category'})2) We will use this variable to filter:
Filter(Locations,varcSelectedEventCategory in 'Supported Event Categories'.Value) Now we see the ComboBox_LocationsVariable Combobox populated with the right filtered options:
Please know that the Power Apps team at Microsoft is putting quite some effort into fixing this “scoping” behavior.
Let’s hope you will never need this workaround 💪👍.
