Working with Optionset lables in Power Automate

Connector Types

Power Automate currently offers 3 different connectors, to connect Power Automate with your Dynamics 365 instance:

• Dynamics 365
• Common Data Service
• Common Data Service (current environment)

Today we will look on ways how to retrieve option set labels via the different connectors, for our sample accounts:

Dynamics 365

This connector should not be used any more, since in April 2019 it has been announced as being deprecated.

https://flow.microsoft.com/en-us/blog/nine-new-connectors-in-april-and-improved-visibility-into-trigger-failures/

With the Dynamics 365 connector it was quite easy to work with optionsets, since it offered both the optionset value (int) and the optionset label (name) in the dynamic content:

And here we go, these are our results:

Common Data Service

Since the Dynamics 365 connector has been deprecated, this is the connector you should use.

If you perform a "Get record" or "List records" action, the optionset value and the optionset label are both part of the JSON returned by the action. Though only the optionset value can be chosen from the dynamic content:

So lets have a look at the content of the returned JSON:

As you can see, the lable of the optionset is there, so how can we reference it in Power Automate?

The solution is to define an expression like items('_<name of apply action>')['<name of the field>_label'], e.g. items('ApplyEachAccount')['_customertypecode_label'] in our case:

And again these are our results:

Common Data Service (current environment)

The Common Data Service (current environment) connector is only offered, if you add your flow to a solution.

It offers more actions than the "normal" Common Data Service connector, e.g. you can use the new AI features (predict), perform actions in CRM, get file or image content, a.s.o.:

And it offers a more advanced "List records" action, where you can e.g. limit the returned attributes via a select query, which should improve the performance of your flow:

It even allows to provide a Fetch XML statement as your search criteria, so you can define your search in advanced find in CRM and just have to download the Fetch XML statement:

BUT unfortunately the List records action does not return any optionset labels with this connector:

Which means, there is no easy workaround, like in the above sample for the "normal" Common Data Service connector to access the optionset labels via an expression.

So I would suggest, if possible, to use the "normal" Common Data Service connector, in case you have to work with optionsets.

BUT, if you really want to go the hard route you can:

Query stringmap table

The stringmap table, is the table that contains the metadata behind your optionsets and it can also be queried via a List records action.

First we initialize a variable of type array to store the result of our "List records" action.

Then we query the stringmap table for all values of the relationship type optionset by providing the entity (=objecttypecode) and the field (= attributename) in the filter:

As a next step we loop over all results and append them to our array variable, to have a dictionary to lookup the optionset labels based on the values:

Then we perform a "List records" action to query all accounts, loop over all returned accounts and filter the array based on relationship type:

Finally we can access the optionset labels via the following formula:

And here we go: