david wyatt

Posted on Oct 14

Working With Dataverse Tables in Power Automate

#powerplatform #powerautomate #dataverse #lowcode

Although we all love a good SharePoint list for our Power Automate Flow, they have some limits, and that's where Dataverse comes in.

Dataverse is where Microsoft wants you to save your data, its a Low code database so is designed to be usable just like SharePoint, but with some additional cool features.

So lets dive in how to use Dataverse in Power Automate and talk about the following actions:

List Rows
Add/Create Row
Relate Rows
Bound/Unbound

Before we start quick call out, Dataverse by default works on the Power Platform environment your in, so if you reference the 'Account' table, it's the 'Account' table in that environment. This means when you deploy to other environments the flow automatically links to the tables in the environment (no need for environment variables). It means custom tables need to also deployed to other environments, luckily you just add them to a solution like any component.

But not quite, as there is also a version of most actions that works across environments. So now you can connect with a table in a different environment. These connector do require a environment variable, but not a list variable like in SharePoint, as it uses the name which is the same across environments.

1. List Rows

The list rows follows a very similar path to SharePoint with the standard:

Select: chose which columns/fields to return
Filter: filter rows by column(s) conditions
Sort By: sort rows by column(s)
Expand: included column from lookup tables
Skip token: url passed from previous results that returns next page
Partition ID: for elastic/file database tables (for another blog)

There is also Fetch Xml Query, which does all of the above in xml, but that's also for another blog.

You can also do some concatenations, like only selected columns from a expand. As an example if you want to get the fullname of who created a flow. In the workflows table the createdby column stores the guid from the lookup table (in this case the users table). To get the fullname we need to expand the createdby column, but this returns all columns (167 to be exact). It also includes urls for other lookup columns, and then the response looks like this (just for one record!).

I lost the will to count the columns

This is where we concatenate a $select with the expand.

createdby($select=fullname)

And now we only get 8 columns, which massively improves performance.

Lookup columns are like light, they are both a record and a string. In the table its the GUID from the lookup table, but in responses its a record, and without a expand its limited to a few columns.

In Power Automate UI it only shows Type and Value columns from the dynamic list.

['_createdby_value@Microsoft.Dynamics.CRM.lookuplogicalname']
['_createdby_value']

It returns the table name (lookuplogicalname) and the GUID (_table_value).

The _table_value pattern is repeated for all lookups, when it works it can be used to get the GUID that can then be used to query that table, and is also useful for filters:

2. Add a New/Update a New Row

Most inputs to a New/Updated row are simple, just drop in the required value, with the exception of choices, lookups, and attachments.

Choices
Choices are lists so can be identified by a dropdown selector inside the action.

Standard Yes/No columns are straight boolean so we can skip them, but proper choices are a little more complex.

For these we do not return the Label but the Value. So below we can see a choice, to set it in a flow we use the integer value.

Flow input

Flow peek code

Lookups
Now these are the fun ones, you can spot these by brackets in the label.

What the bracket is telling you is the table the lookup is linked to (kind of, but will explain more on that later).

So in the above you can see a custom table called 'Deployment Register' and we are using a key column called 'Solution' to look it up.

You would expect a simple input of the value from the Solution column word work. Solution would be a GUID and if from another table most likely _solution_value. But that doesn't work, you need to follow the API's pattern and actually input the table name.

Examples

accounts(accountid)
contacts(contactid)
systemusers(systemuserid)

This is nice and easy for system and CDM tables but a little more complex with custom tables like our Deployment Register. This is because we are not using the display or logical name of the table, but the EntitySetName. For Microsoft tables these match the name but with either a 's', 'es'. or 'ies', but custom can include prefixes.

To get the EntitySetName go to the table in the Tables/solution section, select tools and click 'Copy set name', this will add the EntitySetName to your clipboard.

So for our Deployment Register table our EntitySetName is:

cr4fe_deploymentregisters

I also said "kind of, but will explain more on that later", well that's because some tables are fake tables. The above you see 'Owning Business Unit (Business Units)' and 'Owner (Owners)', so you would think there is a Business Units and Owners table, but there isn't. They actually use the User (systemusers) table and the Team (teams) table, so you pass them instead.

Attachments
Attachments are also a little different as they are not actually stored in the table/record (files are even charged different per mb to records, I suspect this is because they are stored in Azure blob storage).

This means you can't just add the files binary/base64 data to the record, you can't even use a Add a New/Update a New Row action, you have to use a different API/action.

You use the 'Upload a file or an image' action, and pass in the table GUID and the file column name.

The data has to be a binary, so you might have to convert it like:

base64ToBinary(body('Download_a_file_export')['$content'])

Inside the table the file name and a GUID is stored, but there is no other link to where the file is stored.

3. Relate Rows

Relate rows creates a relationship between 2 rows in 2 tables. The best example I can think of is security roles for a user, to give a user a security role we create a relationship with the role (that's right a security role is stored in a Dataverse table too).

To create the relationship we select the required table, this will then populate the list of available relationships (as these need to be created for the table).

Then we enter the GUID of the record from the table with the relationship (i.e. the Security Roles/roles table):

4. Bound/Unbound

The Bound and Unbound actions are in a nut shell custom api's. They run code and complete a function, and are either core (created by Microsoft), plugins (custom api built with c#) or Low-Code plugins (custom api built with Power FX).

The difference between bound and unbound relates to if the api call is bound to a Dataverse record or not.

As an example a bound action for the process/workflows table (where Power Automate flows are stored) include the following actions (as they are link to a particular flow):

Where as unbound actions are not linked to a record, so are only impacted by the inputs. As a good example is the ExportSolution action, this creates a zip file from a solution, and is the exact same api the make.powerautomate UI uses when you export a solution.