This tutorial is to show how we can work with Dataverse Lookups and Choices columns within Power Automate. Many use cases require updating or creating new Lookup or Choices values when creating or updating Dataverse data records. First, we should know that the Lookup columns hold the related records’ GUID values and the Choice columns hold the integer values of the Choices lists. So, when trying to update or create values in those tables, we have to keep in mind that we are working with the GUIDs and Integer values and not the actual display label or text values that appear on those columns if we are accessing the data from the editor in the Dataverse.
To clarify this point, let’s take a look at the table that we will use in this tutorial. Our table is called Order Items and has the below custom columns:
As you can see, we have two tables that will work within this tutorial: The Item Flavor column and it is the Choice Column, it is using the list of items below:
The second column is the Lookup column Order Number which is linking the records in this table (Order Items) to the parent records in the Orders table. Now let’s take a look at the Data as it appears in the table editor in Dataverse:
As you can see the Item Flavor Choice column has the label of the Choice list and the Order number-Order Items Lookup column ( has the text value of the primary name column. Now from the look of these columns and this data, one would think that we can just use the data values as they appear within the Power Automate flows but that is not the case because, as I mentioned earlier, Lookup and Choices columns actually don’t hold those data values as they appear in the screenshot above, rather, in the case of Lookup columns, they hold the GUID values of the related records, and the Choice Item integer values in the case of the Choice columns.
POWER AUTOMATE FLOW
Now let’s jump into our flow in this tutorial which will be an instant flow that can be triggered manually. I have created a flow that will require input from a dropdown list that has: Create, Update, Read then Delete items. After selecting an input, I will be using a switch to determine the following actions.
In this case, I will get the order number 2022-04 and then create a new Order Item the in Order items table that is linked to this order number.
I will list all the order items with a specific flavor item from the choice column using the integer value of the choice item as you can see above. Then I will update all those order Items with another flavor and that will change the flavor for all the order items that are linked to different orders.
In this case, I will get the order number 2022-08 from the order table and then I will get all the related order items. Then I will delete those order items for this order.