Dataverse Option Sets: Picking in Power Automate

Author:

What are option sets?

Whether it is an out-of-the-box features like status fields or a customized picklist to support your business processes, option sets are important for the low-code warriors using tools like Power Automate & Logic Apps to build automations in their Dataverse environments.

For those knowledgeable of the component, option sets have two values: a label value and an integer value.

Below is an example of an option set being edited in the Dataverse designer:

 

Dataverse Option Sets
Look at all these choices!

 

When a user interacts with an option set in a model-driven Power App, they only see the label:

Dataverse form with optionset

 

The behavior is similar when setting the option set in Logic Apps or Power Automate:

Dataverse option set in Power Automate or Logic Apps

 

So, we should expect the same behavior when using the option set as dynamic content in our process:

Dataverse option set in a compose action in Power Automate or Logic Aps

 

Except, this returns the integer value!  This value could be useful, but more than likely the folks working in low-code are hunting for the label value.

A dataverse option set integer populated in a logic app/flow compose step

Where did the labels go?

One feature of the Dataverse connector is the ability to view the raw data of the action performed. Once your process has run, selecting the ‘Show raw outputs’ button on an action allows you to dig into JSON representation of a record:

Raw output of a dataverse action in Power Automate or Logic App to find a dataverse option set

There are a lot of extra goodies in the raw outputs, but we are looking for our option set labels.

There it is! The fabled lost label.

"cr728_optionsetexample@OData.Community.Display.V1.FormattedValue": "And finally, another label",
"cr728_optionsetexample": 720470003,

Well, we can see that the label is in the data. But, how do we use it?

Expressions to the rescue! 

The expression editor isn’t just for math and concatenation. It can also be used to select attributes that are not readily available in the dynamic content pane.

To do this, I recommend highlighting and copying (CTRL + C) or cutting (CTRL + X) the attribute value from somewhere. If you’re brave, you can attempt to transpose by hovering over the attribute to see the actual value:

highlighting an dataverse option set attribute in Power Automate/Logic Apps

 

Select the parameter you wish to populate and paste it (CTRL + V) into the expression editor:

Dataverse option set attribute in the expression editor of Power Automate/Logic Apps

 

Now, we will need to do some cleaning. First, remove everything highlighted in a lovely neon purple:

@{outputs('Add_a_new_row')?['body/cr728_optionsetexample']}

Then, add the information we found in the raw outputs:

outputs('Add_a_new_row')?['body/cr728_optionsetexample@OData.Community.Display.V1.FormattedValue']

 

If you have populated it correctly, you can select ‘OK’ on the expression editor and your inputs should look like the example below:

A dataverse option set label attribute populate in a compose action

We can run our Power Automate or Logic App process and…

Voila, that tricky label appears!

A power automate/logic app compose action with an input of 'And finally, another label from the dataverse option set

A more dynamic option: String Maps

If you aren’t interested in modifying dynamic content in the expression editor or are looking for a way to retrieve more than one option set value at a time, another option exists in the string maps table.

We can access the string maps table through a Dataverse ‘List Rows’ action with the table name parameter set to ‘String Maps’:

 

Power automate list rows with the string maps table selected (where dataverse option sets are stored)

The String Maps table is big since it contains all of the option sets in the entire database, so I recommend filtering down to a specific object type (singular schema name of a table) and maybe including the option set integer value, if applicable:

 

List rows action with odata filter for objecttypecode eq 'account and attributevalue eq 720470003 to find a specific dataverse option set

If we run our process with this criteria and review the outputs:

"value":
[{
"@odata.type": "#Microsoft.Dynamics.CRM.stringmap",
"@odata.id": "https://org4741db43.crm.dynamics.com/api/data/v9.1/stringmaps(9c2250c9-2568-ed11-9561-6045bd006cf4)",
"@odata.etag": "W/\"10477023\"",
"@odata.editLink": "stringmaps(9c2250c9-2568-ed11-9561-6045bd006cf4)",
"value": "And finally, another label",
"attributename": "cr728_optionsetexample",
"versionnumber@OData.Community.Display.V1.FormattedValue": "10,477,023",
"versionnumber@odata.type": "#Int64",
"versionnumber": 10477023,
"langid@OData.Community.Display.V1.FormattedValue": "1,033",
"langid": 1033,
"objecttypecode@OData.Community.Display.V1.FormattedValue": "Account",
"objecttypecode": "account",
"attributevalue@OData.Community.Display.V1.FormattedValue": "720,470,003",
"attributevalue": 720470003,
"stringmapid@odata.type": "#Guid",
"stringmapid": "9c2250c9-2568-ed11-9561-6045bd006cf4",
"organizationid@odata.type": "#Guid",
"organizationid": "c1c318d3-6836-4ebb-80d1-e09db2f077a4",
"displayorder@OData.Community.Display.V1.FormattedValue": "4",
"displayorder": 4,
"organizationid_organization@odata.associationLink": "https://org4741db43.crm.dynamics.com/api/data/v9.1/stringmaps(9c2250c9-2568-ed11-9561-6045bd006cf4)/organizationid_organization/$ref",
"organizationid_organization@odata.navigationLink": "https://org4741db43.crm.dynamics.com/api/data/v9.1/stringmaps(9c2250c9-2568-ed11-9561-6045bd006cf4)/organizationid_organization",
"userentityinstancedata_stringmap@odata.associationLink": "https://org4741db43.crm.dynamics.com/api/data/v9.1/stringmaps(9c2250c9-2568-ed11-9561-6045bd006cf4)/userentityinstancedata_stringmap/$ref",
"userentityinstancedata_stringmap@odata.navigationLink": "https://org4741db43.crm.dynamics.com/api/data/v9.1/stringmaps(9c2250c9-2568-ed11-9561-6045bd006cf4)/userentityinstancedata_stringmap"
}]

There is a lot of unnecessary information for our purpose of retrieving a label value. Let’s clean up our outputs with some odata select filtering. For fun, we will even replace our hard coded value to a dynamic value:

String maps table in dataverse filtered down even more

This results in increased readability in our detailed outputs:

"value": [
{
"@odata.type": "#Microsoft.Dynamics.CRM.stringmap",
"@odata.id": "https://org4741db43.crm.dynamics.com/api/data/v9.1/stringmaps(9c2250c9-2568-ed11-9561-6045bd006cf4)",
"@odata.etag": "W/\"10477023\"",
"@odata.editLink": "stringmaps(9c2250c9-2568-ed11-9561-6045bd006cf4)",
"objecttypecode@OData.Community.Display.V1.FormattedValue": "Account",
"objecttypecode": "account",
"attributevalue@OData.Community.Display.V1.FormattedValue": "720,470,003",
"attributevalue": 720470003,
"attributename": "cr728_optionsetexample",
"value": "And finally, another label",
"stringmapid@odata.type": "#Guid",
"stringmapid": "9c2250c9-2568-ed11-9561-6045bd006cf4"
}
]

This information also available in the dynamic content pane:

Dynamic content showing our option set value is available

Pro-tip: Working with arrays

As a result of using a list rows action, the data is returned in an array. We can determine this in two ways:

  1. from the "value" [] around the list of data in the raw outputs and
  2. Once an attribute from a list rows action is utilized as dynamic content, it is wrapped in an ‘apply to each’ action

option set value in apply to each loop

Its important to remember that when working with arrays, actions that utilize content from them will be place inside a for each loop. This may be desirable when you are expecting to work with multiple values.

Out of the loop

If you are anticipating a single value and wish to avoid placing your actions in a for-each loop, then you can craft an expression that selects the attribute from the first value in a array.

For example, if we want to retrieve the attribute ‘value’ (the string label of our optionset) from the first record returned by the list rows action, we would use the following expression: outputs('List_Rows')['body']['value'][0]['value']

The highlighted section of our expression tells us what content we from the action ‘List Rows’:

outputs('List_Rows')['body']['value'][0]['value']

If our expression was only the highlighted portion, these is what would be returned:

"body": {
"@odata.context": "https://org4741db43.crm.dynamics.com/api/data/v9.1/$metadata#stringmaps(objecttypecode,attributevalue,attributename,value)",
"#Microsoft.Dynamics.CRM.DeleteMultiple": {
"title": "DeleteMultiple",
"target": "https://org4741db43.crm.dynamics.com/api/data/v9.1/stringmaps/Microsoft.Dynamics.CRM.crmbaseentity/Microsoft.Dynamics.CRM.DeleteMultiple"
},
"@Microsoft.Dynamics.CRM.totalrecordcount": -1,
"@Microsoft.Dynamics.CRM.totalrecordcountlimitexceeded": false,
"@Microsoft.Dynamics.CRM.globalmetadataversion": "10525321",
"value": [
{
"@odata.type": "#Microsoft.Dynamics.CRM.stringmap",
"@odata.id": "https://org4741db43.crm.dynamics.com/api/data/v9.1/stringmaps(9c2250c9-2568-ed11-9561-6045bd006cf4)",
"@odata.etag": "W/\"10477023\"",
"@odata.editLink": "stringmaps(9c2250c9-2568-ed11-9561-6045bd006cf4)",
"objecttypecode@OData.Community.Display.V1.FormattedValue": "Account",
"objecttypecode": "account",
"attributevalue@OData.Community.Display.V1.FormattedValue": "720,470,003",
"attributevalue": 720470003,
"attributename": "cr728_optionsetexample",
"value": "And finally, another label",
"stringmapid@odata.type": "#Guid",
"stringmapid": "9c2250c9-2568-ed11-9561-6045bd006cf4"
}

 

The highlighted portion here outputs('List_Rows')['body']['value'][0]['value'] specifies that we want to retrieve the first record in our list/array of "value":[].

If our expression included everything up to and including the highlighted portion, the response would contain this information:

{
"@odata.type": "#Microsoft.Dynamics.CRM.stringmap",
"@odata.id": "https://org4741db43.crm.dynamics.com/api/data/v9.1/stringmaps(9c2250c9-2568-ed11-9561-6045bd006cf4)",
"@odata.etag": "W/\"10477023\"",
"@odata.editLink": "stringmaps(9c2250c9-2568-ed11-9561-6045bd006cf4)",
"objecttypecode@OData.Community.Display.V1.FormattedValue": "Account",
"objecttypecode": "account",
"attributevalue@OData.Community.Display.V1.FormattedValue": "720,470,003",
"attributevalue": 720470003,
"attributename": "cr728_optionsetexample",
"value": "And finally, another label",
"stringmapid@odata.type": "#Guid",
"stringmapid": "9c2250c9-2568-ed11-9561-6045bd006cf4"
}

 

And our final highlighted portion: outputs('List_Rows')['body']['value'][0]['value'] allows us to select a specific attribute in that record. In this scenario, that is: "value": "And finally, another label".

We can place the expression in a compose action:

Power Automate/Logic App expression in a compose step

And when we finally run our example we should see our option set label:

Power Automate compose step results

I hope you found this information valuable and if you are looking for more content on Dataverse features that are easy in the front-end, but tough in Power Automate, check out my post on Dataverse Lookups in Power Automate.

Leave a Reply

Your email address will not be published. Required fields are marked *

en_USEnglish