Jul 23, 2020

Update Multiselect Option sets by label values dynamically in Power Automate

Option sets are tricky fields to work with, both with Power Automate, reporting and 3rd party integration tools. I’ve done by fair share of integrations, reporting and worked with Power Automate for some time and it’s never as straight forward as you think.

After a recent question at a Virtual Power Group Scotland got me thinking about option sets in general as well as multiselect optionsets. Multiselect optionsets are a much requested feature for D365/CDS but values are stored as comma separated integer values. In fact, all optionsets have 2 attributes, Label and Value. The value is how it’s stored in the database and then the label is the user friendly attribute which people see, but these are stored in a separate part of the database. This means when you add an optionset to a form, the label that is displayed, looks up the entity, looks up the value and then shows the user the label. Simple right?

When it comes to Power Automate, as the value is the only thing really stored on the record, this could prove troublesome. But, there are ways to get label values so these can be used in reporting/emails or elsewhere in Power Automate. I’ve made some videos on this here:

Get optionset labels from CDS Triggers

Get optionset labels from CDS Get Record action

Get optionset labels from CDS List Records action

I wanted to create a good solution that could be replicated to make working with multiselect optionsets easier. Trying to marry up integer values to labels is difficult, so I’ve written a flow which allows you to use labels, lookup and dynamically set the correct values in a multiselect optionset.

Firstly you need a trigger, this part does not matter so much, it can be anything. Next we initialise two variables, we need actual variables as we will be looping through them and therefore we. The first variable we need is to store some data for the FetchXML query we will be doing later and we’ll be injecting this data into it.

The next variable will be used to store the integer values of the mutliselect optionset.

Next thing we do is a select action, this is so we can switch the input from an array into objects so we can loop through these later. The input and input item in my example are from the optionset I configured in my manual trigger for my flow.

We then parse the JSON of the objects we are getting, this gets us the individual labels that were selected in the trigger.

Using the output of this, we can concat the labels together building up a dynamic query for our FetchXML. We use an append to string variable action with the FetchXML variable we initialised at the start and concat the labels. The Contact formula I use is: concat('<condition attribute="value" operator="eq" value="',item()?['Name'], '" />')

This formula is concating three parts, the condition attribute, the label and the close brackets.

This helps build condition parts for our FetchXML, dynamically, which means when we do a list records step, we only pull back the values we need. This is powerful!! The List Records step is querying the String Maps entity, this entity is where the labels and values are stored per entity. So if you have the same optionsets across multiple entities, you can get the labels and values back by querying this entity.

The FetchXML query specifies the optionset name and the entity, but you could always get these dynamically as well. We use the FetchXML variable in the “Or” filter.

<fetch>

@{variables('FetchXMLQuery')}

</filter>

</entity>

</fetch>

We now have all rows/records which contain the labels and values we need. We have another loop but this time we are updating the other variable, concating the multiselect optionset values together and separating them with commas. This is the format we need to write it back to a record in D365/CDS.

This is the concat formula for the multiselect values: concat(items('Apply_to_each_MultiSelectValue')?['attributevalue'], ',')

Then we just write/update a record. In this example, I’m updating an existing record but this works for creating records as well. I’m updating the optionset called “Super Hero Affiliations”. I am writing a formula to this field and not the variable directly. The reason for this is we need to remove the final comma at the end of the variable, as we were adding commas after every value. This formula is:

substring(variables('MultiSelectOptionSet'),0, sub(length(variables('MultiSelectOptionSet')),1))

What this formula does is checks the total length of the variable and returns the total length minus 1 character. Thus taking the last comma off and allowing you.

This is my solution for this, I hope you find it useful and can adapt it to your needs. Here's a video if you'd prefer to see this in action.

Ciao for now!