Don’t Update or Insert records in Dataverse but Upsert when using Power Automate
A typical pattern for data import processes is that you first check if an item exists before either inserting or updating the found item. Upsert (Update or Insert) the item in one operation is however better.
Update or Insert
So the base process could look something like this. We first get the flow to check if the item already exists and then when it doesn’t exist we can add the item. However there is a bit of time between those two steps and things may have changed.
In the above example I would every now and then get failures in my high volume flow.
A record with matching key values already exists.
So how do we make this flow run more reliably?
Upsert action
Before you look for an upsert a row action in Power Automate, this doesn’t exist. Well actually it does exist under a different name.
In my solution I’m reading call records using Microsoft Graph in an HTTP request. Using the data returned I’m now doing an update a row using the id that I’ve been given by my Graph API call.
It is important to realize that the id used in the Row ID is a unique and in the standard GUID format. The external system (Call Records) is responsible for delivering unique guids to me and my Dataverse tables will inherit these unique ids.
The Update a row action will now look at my table and find the matching record to the id supplied. If this record exists we get the expected update of the record. If however the record doesn’t yet exist, a new record with that GUID is created.
Why not use Dataflows?
Well, that is a good question. For simple data imports, I would probably use dataflows. However for complex data where my data needs to be moved across multiple lists and potentially the data needs to be adjusted on the way, Power Automate may give the more flexible solution.
Is this method 100% bullet proof?
So far I’ve found that this is not 100% bullet proof but an awful lot better than the method mentioned before.
The error details that I got about once a day or so is shown below:
{
"error": {
"code": "0x80040237",
"message": "A record with matching key values already exists.",
"@Microsoft.PowerApps.CDS.ErrorDetails.SqlExceptionMessage": "Violation of PRIMARY KEY constraint 'PK_new_CallRecordBase'. Cannot insert duplicate key in object 'dbo.new_CallRecordBase'. The duplicate key value is <redacted>.",
"@Microsoft.PowerApps.CDS.HelpLink": "http://go.microsoft.com/fwlink/?LinkID=398563&error=Microsoft.Crm.CrmException%3a80040237&client=platform",
"@Microsoft.PowerApps.CDS.InnerError.Message": "Cannot insert duplicate key."
}
}The above could happen if two flows try to attempt to add the same record at exactly the same time. #Exactly the same time hardly happens in the world of computers so you might never see the above.
Continue Reading Pieter Veenstra’s Article on their blog
Don’t Update or Insert records in Dataverse but Upsert when using Power Automate
A typical pattern for data import processes is that you first check if an item exists before either inserting or updating the found item. Upsert (Update or Insert) the item in one operation is however better. So the base process could look something like this.
Blog Syndicated with Pieter Veenstra’s Permission
More About This Author
Array
Modern Workplace26 March 2024Receive the available storage within your SharePoint Online tenant- Modern Workplace20 March 2024Options for Documenting Your Power Apps: Comments, Code, and Controls
- Modern Workplace18 March 20242 ways to duplicate SharePoint Lists to support your Power Apps
- Modern Workplace14 March 2024Update a Hyperlink Column in SharePoint with Power Automate
