Quickly Create a Large Dataverse Choice Set with Power Automate

Have you ever wanted to create a large Choice Set in Dataverse but don't want to copy and paste each item in from a list? We almost always already have our choices in an excel sheet or in some kind of list, why plug them all in one after another manually?

Good news, it is possible to automate this with Power Automate using the Dataverse Web API.

In this post I will show you how to create a reusable flow to generate Dataverse Choice Sets in a solution automatically from a list of choices.

Create a Flow That Accepts Required Choice Set Inputs

Here I'll just use a simple manually triggered flow, but I could also do a Copilot triggered flow and integrate it into a Copilot Agent for easier use, or a Power Apps flow for use in a Canvas App, or an HTTP Request trigger.

Required Inputs

1. Display Name for Choice Set
2. Logical Name (lowercase, no spaces)
3. Options List (comma separated)
4. Solution Name (logical)
5. Description

You could drop the Solution Name input (or make it optional) if you want to hard code the flow to save your choice set to a specific or default solution.

Review the Web API for Choices (Option Sets)

To do this we will need to use "Invoke an HTTP Request" to call a Dataverse Web API.

We will use the "Create a global option set" endpoint as described here.

Endpoint:

[Organization Uri]/api/data/v9.2/GlobalOptionSetDefinitions

Headers:

{
"Accept": "application/json",
"Content-Type": "application/json",
"MSCRM.SolutionUniqueName": "<solution-name>"
}        

Payload:

The payload may look complicated, but it's actually not too bad. For our flow we will need to copy one of the JSON objects in the "Options" array in Microsoft's example, to use as a template for generating the payload for our options set.

Generate "Options" Array for the API Payload

Create an Array from the Input List

Use the split() function.

Create a seed value for the value of each option

Option set choices need a numeric value.

We'll use the same seed value Dataverse uses.

In my Dataverse, the seed value when I create a new choice set manually is 864310000, so I will use that.

I save this value in a Compose.

Use Select to Create the Options Array

I will use the template we looked at previously.

NOTE: We have to 'escape' the '@odata...' properties by adding another @ to each one, otherwise Power Automate will throw an error.

{
      "Value": {{value}},
      "Label": {
        "@@odata.type": "Microsoft.Dynamics.CRM.Label",
        "LocalizedLabels": [
          {
            "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
            "Label": "{{label}}",
            "LanguageCode": 1033,
            "IsManaged": false
          }
        ],
        "UserLocalizedLabel": {
          "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
          "Label": "{{label}}",
          "LanguageCode": 1033,
          "IsManaged": false
        }
      }
    }        

To do this, I just paste this in the Select Map (text mode)

Create an Auto Incremented Value for Each Choice Option

We need to increment the value for each option based on our seed value. Believe it or not, there's a way to do this without a For Each loop. Since we're using a comma separated list to create our options, we can use this to find the numeric index of each item using Split() and Length() functions.

1. Split the comma separated list by the current item
2. Select the first item in the resulting array (everything that comes before your current item)
3. Split this array by the separator to get an array of everything before the current item.
4. Count the items in this array with Length() to get the numeric index of your current item
5. Add the count (our index value) to the seed value.

The expression looks like this:

add(
     length(
          split(
              first(
                   split(
                        triggerBody()?['text_1'],item()
                   )
              ),
              ','
           )
      ),
      outputs('Compose_Seed_Value')
)        

And it goes in the "Value" property of our JSON template.

Add the current Option Choice to the "Label" properties of the JSON Template

This is done by adding the "item()" expression to each "Label" property in the template. To be extra careful in case the user has spaces in their comma separated list, I'll use "trim(item())" for each "Label".

Get the Prefix for Your Solution

Next we need to get the prefix that is automatically added to any table, column or choice set in your solution. Since we're passing the Solution Name in the trigger, we can use that to get the Solution's Prefix with a Dataverse API.

Of course, it's also an option to hard code your preferred solution and prefix into the flow.

Invoke an HTTP Request with "HTTP With Microsoft Entra ID"

Add an "Invoke an HTTP request"

Add a connection and sign in

Use your Organization URL (first part of your url when you open a model driven app) when creating the connection.

https://<organization-url>.crm.dynamics.com

You can also find it under "Developer Resources"

Construct a GET Method Request to the Solution Endpoint

We can filter for our solution name (supplied by the trigger input) and grab the "customizationprefix" property under "publisherid".

/api/data/v9.2/solutions?$filter=uniquename%20eq%20%27<solution-name>%27&$expand=publisherid($select=customizationprefix)        

Save the Prefix in a Compose after the HTTP Request

Use this expression in a compose:

first(
     body('Invoke_an_HTTP_request_-_Get_Solution_Prefix')?['value']
)?['publisherid/customizationprefix']        

Construct the Final Payload in a Compose

Here we will again reference Microsoft's example to complete our Payload JSON object. We are just dropping in references to our prefix+logical name, the display name, the description and the options array.

NOTE: We again must 'escape' the '@odata...' properties by adding another @ to each one, otherwise Power Automate will throw an error.

The code is something like this:

{
  "Name": "@{outputs('Compose_Solution_Prefix')}_@{triggerBody()?['text_2']}",
  "OptionSetType": "Picklist",
  "@@odata.type": "Microsoft.Dynamics.CRM.OptionSetMetadata",
  "Options": @{body('Select_Option_Items_(JSON)')},
  "Description": {
    "@@odata.type": "Microsoft.Dynamics.CRM.Label",
    "LocalizedLabels": [
      {
        "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
        "Label": "@{triggerBody()?['text_3']}",
        "LanguageCode": 1033,
        "IsManaged": false
      }
    ],
    "UserLocalizedLabel": {
      "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
      "Label": "@{triggerBody()?['text_3']}",
      "LanguageCode": 1033,
      "IsManaged": false
    }
  },
  "DisplayName": {
    "@@odata.type": "Microsoft.Dynamics.CRM.Label",
    "LocalizedLabels": [
      {
        "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
        "Label": "@{triggerBody()?['text']}",
        "LanguageCode": 1033,
        "IsManaged": false
      }
    ],
    "UserLocalizedLabel": {
      "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
      "Label": "@{triggerBody()?['text']}",
      "LanguageCode": 1033,
      "IsManaged": false
    }
  }
}        

Invoke the Final HTTP Request to Create the Option Set

Add another "Invoke an HTTP Request"

This one will be a POST to the endpoint "/api/data/v9.2/GlobalOptionSetDefinitions"

Setup the Headers

{ "Accept": "application/json", "Content-Type": "application/json", "MSCRM.SolutionUniqueName": "<your-solution-name>" }

Setup the Body of the Request

The body will be the output of the compose action where we constructed the final JSON payload.

Test

I start a test run. Here I'm creating a choice set for all the States in the USA.

Note: To find the logical name of your solution, check under your "Solution Overview"

In about 10 seconds, the choice set with 50+ options has been created.

It may take a minute for it to show up in your Solution Explorer, but it usually appears soon after a page refresh.

Just to check, click into the choice set to see your options.

Thanks for reading!

If you found this post helpful please give it a like. Power Platform Developer | LinkedIn: Mark Nanneman?| YouTube:?Mark's Power Stuff? |?Buy me a coffee