Dynamically retrieve option sets
Choice fields in Microsoft Dataverse can sometimes be challenging to interact with from Power Automate flows. In this article I will show you how to dynamically retrieve an option set and use the id in the rest of your flow.
Inspiration
This question from mimocnik:
How to handle option sets in Power Automate?
Power Users Community thread: How to handle option sets in Power Automate?.
Microsoft Forms
In this example I am capturing information about a new account in a Register New Address form in the product Microsoft Forms. After that form has been submitted the goal is to create a new account in Microsoft Dataverse and/or Dynamics 365 Sales. As you can see in the screenshot below some fields are of type choice, in this example address type on the account table.
The challenge with this approach is that the Power Automate action field does not accept the labels of the options but requires the ids.
EntityDefinition Approach
After some searching I found that it was possible to retrieve options sets by using Microsoft.Dynamics.CRM.PicklistAttributeMetadata via the Microsoft Dataverse Web API. A StackOverflow response by Ayush Singroul pointed me into the right direction.
Flow setup
1. Add a When a new response is submitted trigger action.
2. Add a Get response details action.
3. Add three Initialize variable actions. Below is a table with the name, type and value for each of the variables.
| Name | Type | Value |
|---|---|---|
| EnvironmentUrl | String | https://yourorganisation.crm11.dynamics.com |
| Table | String | account |
| Column | String | address1_addresstypecode |
4. Add a HTTP action. Use a GET request and the uri from below in the code snippet. In this example we are using the EntityDefinitions table to retrieve the local opionset values for our column.
This action requires an app registration and an application user. Have a look at my previous article on How to use the Dataverse Web API to see how you can set this up.
5. Add a Filter array action. In this action we are using the Options collection field in the From. In the filter we are compary the label with the answer of the Microsoft Forms question.
6. Add a Add a new row action. Use the answers from your Microsoft Forms response to create a new account record. Use an expression with the output of the filter array in a custom value for the Address 1: Address Type field.
Testing the result
When you submit the form you should now see that the flow is able to map the correct address type value.
Hopefully this will make it a bit easier to map your choice fields in the input side of things to the Microsoft Dataverse choice columns! 🙂
Happy testing!
