Use Dataverse Elastic Tables in Power Automate Cloud Flows

In this blog post, I will guide you through overcoming common challenges encountered during CRUD operations on elastic tables in Dataverse using Power Automate cloud flow.

While I was working with cloud flow run history in Dataverse, I experienced an unusual issue. In my cloud flow, I use a "List Rows" action to retrieve the data from Flow Run table (which is using Dataverse’s nonrelational database, elastic tables). The query returned the columns as specified in the documentation.

flowruns(«GUID»)

However, when I used the GUID of the flow run to populate the lookup column in the custom table, I encountered the following error.

The HTTP status code of the response was not expected (404).
"error":
{
	"message": "Could not find item '25b17f77-3abb-9596-5cd3-9275652c7f01'.",
	"details":
	[
		{
		"message": "\r\nErrors : [\r\n "Resource Not Found. Learn more: https://aka.ms/cosmosdb-tsg-not-found""
		}
	]
}

I double-checked the GUID, confirming it matches the flowrunid from the output of the "List Rows" action. I attempted to retrieve the Flow Run record using the "Get a row" action with the same GUID, but I encountered the same error.

After searching through forum solutions and Microsoft documentation, I discovered the article "Use Elastic Tables Using Code."

In the article, it explains the information that we need to set the lookup column which is referring to an elastic table.

🛈 Note

When a table record refers to an elastic table record which has partitionid column value set, you must include the partitionid column value of the elastic table record when you set the lookup column of the referencing table. You can do this by including the value as an alternate key.

As described in Partitionid value column on referencing table, when a one-to-many relationship is created and the elastic table is the referenced table, a string column and a lookup column is created on the referencing table. The string column stores the partitionid value of the referenced elastic table record.

You can set both the lookup and the string column values with their respective values by:

• Using an alternate key reference to set only the lookup
• Setting the two column values together in one update

It's crucial to understand that setting the GUID of the lookup without including the partitionid column value won't trigger an error, but the data won't link correctly. This is why the related columns in the subgrid didn't display properly when I used only the GUID to set the lookup value with the SQL4CDS tool.

⚠ Important

If you choose to use a partitioning strategy for your elastic table, all operations on that table or referring to records in that table MUST specify the partitionid column value to uniquely identify the record. There is no error thrown if partitionid is not specified in the lookup value of referencing table, but the lookup will fail to locate the record when you use it. You must document and enforce this requirement via code reviews to ensure that your data is consistent and partitionid is used appropriately for all the operations.

The article also includes examples of various operations using the Web API, formatted similarly to the parameters in Dataverse actions.

Populating Lookup with GUID and PartitionId

This is the format that I used to populate the lookup to the elastic table with two parameters, one for the GUID and another one for the partitionid (which is similar to populating the lookup with composite alternate key)

flowruns(flowrunid=«GUID»,partitionid='«PartitionId»')

Note: There are single quotes ( ' ) around the PartitionId parameter but not required for the GUID parameter.

Populating Lookup with Alternate Key and PartitionId

You can also use the alternate key to populate the lookup (in this case, name column is a key)

flowruns(name=«AlternateKeyValue»,partitionid='«PartitionId»')

Note: In this case, there are single quotes ( ' ) around both parameters.

Get a Row by ID and PartitionId

Similarly, you can get the data from the elastic table using "Get a Row by ID" action by populating the Partition Id parameter.

Delete a Row by ID and PartitionId

The same logic applies for the "Delete a row" action.

"List rows" action also contains the Partition Id parameter.

Retrieving both GUID and PartitionId of the Lookup

When you retrieve the value of the lookup to the elastic table (e.g. flowrun), you need to retrieve both PartitionId (string column) and GUID (lookup column) on the referencing table because you will need both information in "Get a Row by ID" action (as mentioned above)

The column name of the PartitionId (string column) will be «LookupColumn» + "pid" (e.g. if the lookup column is "lzw_flowrun", PartitionId column will be "lzw_flowrunpid")

In this blog post, I address common challenges with CRUD operations on Dataverse's elastic tables using cloud flows, highlighting the importance of including the partitionid column value to ensure proper data linkage and avoid errors.