In this tutorial, we will show you how to retrieve all the possible values from a Dataverse option set column using Power Automate. Let's get started!
Step 1: Create a Dataverse Request
To start, create a Dataverse request in Power Automate. In this request, we will list the rows from the "string maps" table, which stores all the option set values. We will then select the table and column that we want to retrieve the option set values for.
In this example, we have a "pets type" column in our "account" table with options like "cat," "dog," "parrot," and "Galapagos giant tortoise." To retrieve all the option set values for this column, we will set up the Dataverse request as follows:
• Table: "string maps"
• Select: "attribute value," "value"
• Filter query: "object type code eq [table code] and attribute name eq '[column name]'"
Replace [table code] with the table code for your table (you can find this in the table settings), and replace [column name] with the name of your option set column.
Step 2: Output the Option Set Values
Now that we have set up the Dataverse request, we need to output the option set values. To do this, add a "Compose" action after the Dataverse request.
In the "Inputs" field of the "Compose" action, enter the following expression:
body('Dataverse_request')?['value']
This will output an array of objects, where each object represents an option set value. Each object will have two properties: "attribute value" and "value." The "attribute value" property is the display name of the option set value, and the "value" property is the numeric value of the option set value.
Step 3: Save and Run the Flow
Finally, save and run the flow to retrieve all the option set values from your Dataverse column. The output of the flow will be an array of objects, where each object represents an option set value and includes the display name and numeric value.
