Updating Dataverse Optionset/Choice from Text dynamically using Power Automate

One of my favourite flows in recent memory is how to update a multiselect optionset/Choices column as these are famously not usable in Classic Workflow. I documented how to do this here.

Recently, I received an email from someone who was trying to set a regular optionset/choice column and running into difficulties. I realised that again, like with many aspects of Power Automate, it’s a little more complicated than it should be for people. When you are trying to integrate multiple systems into Dataverse whether it is an SQL database, Customer Voice, Excel or another 3rd party system, you may need to map the Text string to a choice column. However, Dataverse stores choices as values and not text, making this not as straightforward as you may think.

Here is a quick and easy method to set a Choice Column.

Dataverse stores choice columns and values in a table called String Maps. This table specifies the table the choice belongs to (which can be multiple different tables if this is a global choice column), the name of the choice column, the values and the text. We can use this to find the values we want.

Create a list rows action in your flow for the table String Maps. Create some FetchXML for the table and choice column you want to lookup the values in.

Here is my FetchXML:

<fetch>
  <entity name="stringmap" >
    <attribute name="attributevalue" />
    <attribute name="attributename" />
    <attribute name="value" />
    <filter>
      <condition attribute="objecttypecode" operator="eq" value="2" />
      <condition attribute="attributename" operator="eq" value="preferredcontactmethodcode" />
      <condition attribute="value" operator="eq" value="@{triggerBody()['text']}" />
    </filter>
  </entity>
</fetch>

In the above FetchXML there are a couple of attributes to explain:

Objecttypecode - This is the table where the choice column is. In the above example, objecttypecode = 2 means this is the Contacts table.

Attributename - This is name of the choice column.

Value - This is the text in the choice column that we want to match against.

In the above example, I’m using my trigger with a text field to pass in the text value I want to look up.

This will return the value we need and we can set the choice column using this value. Add an Update a Row or Create a Row step and in the choice field we want to set, add a customer value. In the customer value, we need to add a formula:

first(outputs('List_rows_String_Maps')?['body/value'])?['attributevalue']

This formula will take the first result from the List Rows array, as I know there will only be a single value that matches the text I am providing, therefore we can avoid unnecessary loop control.

Next, the outputs('List_rows_String_Maps') allows me to specify the name of the step that we want to get the values out of. Then ?['body/value']) allows me to specify I want to look for something in the body/value of the JSON array returned. ?['attributevalue'] lets me get the specific value I’m looking for, this is the name of the column.

Save it and test it. Thanks Tracey who gave me the inspiration to write this.

I wrote the above FetchXML using the FetchXML Builder by Jonas Rapp. Jonas recently had a stroke so if you have ever used this tool or his many other tools in the XrmToolBox, please consider donating to him for all his efforts. http://fetchxmlbuilder.com/donate

Ciao for now.