Accessing Dataverse records by ID in Power Automate
Have you ever got confused when using the Dataverse “Get row by ID”, “Update a row” or “Delete a row” actions in Power Automate?
When integrating Power Automate with Dataverse, a common challenge is the correct reference to the record ID for reading or updating data. Many users get confused due to naming conventions adopted by some tables, where the “user-friendly id” column (or Primary Column) doesn’t precisely align with what Power Automate necessitates for accessing these records.
As Power Platform relies on GUID references to interact with Dataverse records, it is important to understand how to identify these properties across various tables, particularly when acquiring the system identifier based on the “user-friendly id”. Fortunately, there is a very straightforward strategy to solve it!
Differences between ID and GUID in Dataverse
All Dataverse tables have a “Primary column”, serving as a user-friendly column where a unique ID is stored. The unique ID is an individual identifier for a record and, by definition, does not accept duplicates. It is like a Social Security Number: while multiple people may share the same name, they can never share the same Social Security Number.
For example, when analyzing the “Account” table, we verify that the “Account Name” is the primary column (and unique identifier), and no duplicate records are encountered there:
Attempting to add two records with identical values assigned to the Primary column will result in an error, as it violates the uniqueness of the column records. As in the image below, an Account already exists with “Account Name” set to “abc” (highlighted in yellow), so once we add a new record with the same “Account Name” (highlighted in green), a warning is generated and the row won’t be added to the table:
In the same table, there is also another column called “Account”, which is read-only and also stores alphanumeric values:
This column is a system unique identifier, and it is utilized by Power Platform to reference individual records. Like the user-friendly unique ID, duplicates are not accepted in this column, and making it a read-only column provides an additional layer of security to maintain data consistency. By default, the GUID consists in a 128-bit text string, which is very effective to ensure identifier uniqueness, although it is not easily readable by humans.
This system unique identifier is also called GUID (globally unique identifier), and it is used by Power Automate in actions that read, update or delete Dataverse table records.
Identifying the GUID in Power Automate: just search for the table name
The user-friendly unique ID columns can have various names. For instance, in the “Account” table, it’s referred to as “Account Name”; in the “Recurring Appointment” table, it’s known as the “Subject”; and for custom tables, a wide array of different identifiers can be used.
Fortunately, the GUID column will always be named after the table, ensuring consistency. For example, the GUID column for the “Project” table will be named “Project”, and the same rule extends to custom entities as well. If your organization has a table called “Grade”, the automatically created GUID column will necessarily be called “Grade”.
With this pattern in mind, you can access an individual record by utilizing its unique ID stored in the table’s name column, or alternatively, employ a dynamic content reference to it.
Let’s consider a practical example: suppose we aim to access details from the “Account” table record with “Account Name” set to “abc”. Since we don’t know the GUID for this individual record, we will begin by listing all rows from the “Account” table (use a “List rows” action for that), adding a filter query to restrict the results to records where the “Account Name” column (whose logical name is simply “name”) matches “abc”:
Once we proceed, we will be able to access the information of the “abc” record by adding a “Get a row by ID” action. As “Row id” input, we’ll utilize the GUID column, designated as “Account” (highlighted in blue), rather than providing the user-friendly ID. Another strategy to identify a GUID column is by examining the column description in Power Automate’s dynamic content list: it must contain “Unique identifier of [table name]” (highlighted in yellow).
Following that, we will successfully retrieve all information about the account “abc”:
Conclusion
GUID columns are important for maintaining consistency in Dataverse table records, yet they often cause confusion for users at all skill levels. Fortunately, understanding the pattern in the GUID column name can simplify the lives of Power Automate users, enabling them to easily identify the correct input for Dataverse actions and prevent potential errors in the future.
Originally published at http://digitalmill.net.
