Sometimes in Power Automate you have a text value (string) and you want to use it to set a Choice Column in Dataverse. To do this, you need to get the numeric code value for that Choice Column Option. For large Choice Sets, it's impractical to use conditions. Fortunately there's a quick an easy way to get the required value using a Dataverse Web API endpoint.
Add an "Invoke an HTTP request" Entra ID action
We need to get a list of option values and their matching labels for our Choice Set in order to filter it based on the text on the submitted form. This can be done using a Dataverse Web API, which we can call with the "Invoke an HTTP request" action.
Add a connection and sign in
Use your Organization URL (first part of your url when you open a model-driven app) when creating the connection.
https://<organization-url>.crm.dynamics.com
You can also find it under "Developer Resources"
Construct a GET Method Request to the Solution Endpoint
We will use the "Retrieve Option Set" GET endpoint listed by Microsoft here.
/api/data/v9.2/GlobalOptionSetDefinitions(Name='<option-set-name>')?$format=application/json;odata.metadata=none
You will need to put the logical name of your Dataverse Choice Set in the endpoint after "Name='".
You can find the logical name of your choice set in Dataverse.
It will look like this in your flow:
Filter the Returned Options Array for a Label that Matches the Text Submitted
When the "Invoke an HTTP request" runs the Dataverse Web API Endpoint will return a complex JSON object defining the Choice Set with another complex array of Options for it.
We need to filter the included "Options" array to find the item with a label that matches our search string, so that we can grab the "Value" property of that item.
From:
Select the "Options" array from the HTTP response like this:
body('Invoke_an_HTTP_request_-_Get_Choice_Set')?['Options']
First Parameter:
The label is stored in each Option item under the following path, which we use in the first parameter of the filter:
item()?['Label/UserLocalizedLabel/Label']
Second Parameter:
The second parameter is the search string, in our case the "State" name submitted on the form.
Code View:
Get the Value of the Item Returned by the Filter
The filter will return the entire JSON object of the item with a matching Label. We only need the "Value" property.
Use a compose to grab this.
first(body('Filter_array_-_Options'))?['Value']
Input this Integer on the "Add a new row" Action
Thanks for reading!
Power Platform Developer | LinkedIn: Mark Nanneman | Wordpress | YouTube: Mark’s Power Stuff | Buy me a coffee
Top comments (0)