In this article I’ll share my experience and everything I learned about Option Set in PowerApps. Check out my cheat sheet.
Prepare for a battle
Before I start a project I prepare myself and gather all information that helps me to avoid getting into technical dead ends, improve project delivery pace and work efficiency. I read documentation, articles, watch tutorials and do proper exams (for PowerApps & CDS I can recommend MB-200 exam). Like Abraham Lincoln once said:
At the same time, no matter how hard you try you won’t be prepared to everything. Especially in IT projects where you can expect the unexpected. Sometimes you’ll need to solve those corner cases which are “very specific only to your project & to this very unique setup” only you have. But sometimes you just try to do some “ordinary operation” (at least as you think about it) and you discovered that it’s not possible or at least is not easy. The common reason for such situations is that the functionality or feature is so new that documentation does not cover it sufficiently and you can’t find ANYTHING (video, article, blog) that completely covers the topic.
Option Sets for PowerApps Canvas apps are one of those topics and this article is about to group all information together.
CDS Option Set cheat sheet for PowerApps
Did you know: You can contact me if you need my help with PowerApps and Flow. I conduct trainings, consultations, build PoC or Solution and also make an audit of your PowerApps app.
|What is Option Set?||A field type|
|What it contains?||List of text values|
|Technical synonyms||A dictionary, an array of enums|
|Main pros||Centrally managed|
|Dynamic extending||No, only predefined values|
|When not to use it?||Case 1:
If you need to reference to Option Set values without creating connection to Entity with this Option Set.
For local usage use Collection.
For global usage use Entity.
You need additional metadata to Option Set values (ie. Country Population)
Solution: Use Entity.
Option Sets in PowerApps
In PowerApps Option set is one of the field types you can use in your Entity. The information type that Option Set stores is a list of text values. And here comes the Option Set advantage – once you define its text values you can centrally managed it.
Imagine you have a list of countries you have offices in. Once you define such list you can use it in any of your apps. And even more than that – you can use it in PowerBi reports, Flow, Dynamics365 and all other applications that can integrate with CDS.
CASE 1: You’re doing so well that you’re opening a new office in a new country. Would you update all solutions that use your Option Set? Of course not! You only need to update your Option Set definition.
Case 2: You’re doing even better and your office in England start to operate all over United Kingdom. Would you add new text value to a list and update all solutions that use your Option Set? Of course not! You only need to rename England to United Kingdom. And that’s it – this is possible because technically speaking Option Set is a table with 2 columns: Id and Text.
The Text column is only for us – the humans. The Id is used by them – computers, programs, solutions etc. And this is where all misunderstandings begins. People are confused “why I can’t use a text value to set Options Set value? Machines are confused “why humans does not appreciate I can distinguish 2 options even if they have the same text value?”
Yes, in CDS Option Set you can have 2 items with the same name. CDS will use Id value to differentiate them.
How to work with the field in PowerApps and Flow?
Let’s split the above question on 2 parts:
- How to reference an Option Set Value in Microsoft Flow?
- How to use Option Set field in PowerApps?
- Option Set with Dropdown
- Filter by Option Set field
- Update Option Set field using Patch function
How to reference an Option Set value?
Long story short: use the item Id. How to find it?
- In PowerApps website open Option Sets
- Open your Option Set
- Click on “…” > View More
Elaiza Benitez recorded a video where she shows how to use option set value in Flow so I’ll skip reinventing a wheel and just give you a link to the video already starting in the right time.
How to use the field?
Option Set with Choice dropdown
Using Choices function with a name of Option Set works great.
Filter by Option Set field
Use special [@ColumnName] reference that directly point to the Option Set. You’ll see the Option Set values you can easily choose from.
Disclaimer: Last year there was anupdate that improved how PowerApps makers could reference to Option Set field values but…somehow it doesn’t work for me no matter on how many tenants I tried that :(. But I’m sure sooner or later described in above article method (that is reference using ‘OptionSetName (Entity Name)’.Value) will work so stay tuned and treat my post as workaround.
To turn this feature on go to App Settings > Advanced Settings > “Relational data, option sets and other new features for CDS”
Update Option Set field using Patch function
Use the same method as I’ve described in previous section (use [@ColumnName].
Following formula works for me like a charm:
Hope this will be useful for you.
If you have any questions – let me know.