Don’t Update or Insert records but Upsert in Dataverse when using Power Automate

An Upsert in Dataverse can be implemented in two different ways. Check before you do or just a plain Upsert action.

Update or Insert

A typical pattern for data import processes is that you first check if an item exists before either inserting or updating the item found. Upsert (Update or Insert) the item in one operation is however better in many situations.

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 flows. If you have for example multiple flows that try to update or insert records into the same Dataverse table and neither of the two flows is aware of the other one. The following error is quite a common result.

A record with matching key values already exists.

So how do we make this flow run more reliably? I’ve seen people adding all sorts of waiting and retry sections in their flows, but they are not the solution.

One 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 as 99% of the cases the Upsert will not happen at exactly the same time. Compared to the “check first then either create or update”-method the Upsert is a lot more reliable.

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 error. And worst case add a bit of error handling into your flow. Then any failure could result in a retry of the upsert being scheduled a short period after the failure.