In Power Automate, getting all the option set values from Dataverse is not quite easy as getting the selected value from the option set field(Dataverse Choices). Here in this article, we will have a look at how to take all the options from the option set and list it using Power Automate(Formerly Flow).
Steps
- Get Option set metadata from Web API
- Creating a flow
- Invoking HTTP request
- List all options
Get Option set metadata from Web API
Generally, we retrieve all the option sets from the Dataverse using the API in the Browser or Postman.
https://<org_name>.crm.dynamics.com/api/data/v9.2/EntityDefinitions(LogicalName='<entity_name>')/Attributes(LogicalName='<field_name>')/Microsoft.Dynamics.CRM.PicklistAttributeMetadata?$select=LogicalName&$expand=OptionSet($select=Options)
In the above HTTP REST API url replace the text that are enclosed within the angle braces. The entity name and field name should be the logical name. The response for the API endpoint looks like the below picture.
We should replicate the above operation in Power Automate using the HTTP connector
Creating a flow
For creating a Power Automate flow, open your Dataverse solution. Click on + New on the command bar. Automation? Cloud flow? Click Instant.
A modal window named Build an instant cloud flow opens up, choose a name for your flow and choose the trigger you wish. I have chosen Manual trigger because I didn’t want it to execute from another trigger. Click on Create button. Then the flow edit page opens with the trigger step.
Invoking HTTP Request
We can retrieve the Option set metadata in Power Automate by invoking a HTTP GET Request. Click on the + icon and search for the connector name Invoke an HTTP request. Connect it with the base-url and click Sign in.
After the connection gets established, select the method as GET and use the Web API URL in the Url of the request.
body('Invoke_an_HTTP_request')?['OptionSet']?['Options']
In the Parse JSON connector, use the above expression in the Content field and for Schema field copy the below JSON snippet and click generate from sample button and paste it and click Ok.
{
"Value": 1,
"Color": null,
"IsManaged": true,
"ExternalValue": null,
"ParentValues": [],
"Tag": null,
"MetadataId": null,
"HasChanged": null,
"Label": {
"LocalizedLabels": [
{
"Label": "Accounting",
"LanguageCode": 1033,
"IsManaged": true,
"MetadataId": "6798ba00-2341-db11-898a-0007e9e17ebd",
"HasChanged": null
}
],
"UserLocalizedLabel": {
"Label": "Accounting",
"LanguageCode": 1033,
"IsManaged": true,
"MetadataId": "6798ba00-2341-db11-898a-0007e9e17ebd",
"HasChanged": null
}
}
Click Generate from sample and paste then submit it. Then the flow step looks like the below picture.
List all Options
After parsing the response from the HTTP Request, add a Create HTML Table connector to list the Value and Label in the form of table. In the From field, select response body from the Parse JSON connector.
- item()?[‘value’] is the expression for getting Value
- item()?[‘Label’]?[‘UserLocalizedLabel’]?[‘Label’] is for getting Label Text.
Save the flow and solve if the flow checker is showing any warnings or errors. Run the flow by clicking the Run button on the command bar.
After running, check the run history for the status and open it to see the working of the flow. If the run results in error, check where it got failed and re-run again until the flow get succeeded. After the success of the flow open the run and move down to the Create HTML Table flow and expand it. You will see the list of options in the Outputs section as like in the below picture.
Keep Learning!
c aPosted Feb 12, 2023, 5:48 AM
Https://youtu.be/oL_4I30alEo
c aPosted Feb 12, 2023, 5:43 AM
You don't need to go through Azure AD, you just use String Maps table like in the video above.
S MPosted Jan 11, 2023, 4:10 PM
Hi, Thanks for this very interesting solution. I tried it out. When I execute the http action I am getting following error message: BaseResourceUri (https://abcdgroup.sharepoint.com) must be a base of the full url (https://abcd.crm4.dynamics.com/api/data/v9.2/EntityDefinitions(LogicalName='account')/Attributes(LogicalName='apr_myfield')/Microsoft.Dynamics.CRM.PicklistAttributeMetadata?$select=LogicalName&$expand=OptionSet($select=Options)). Any idea