Don’t Update or Insert records but Upsert in Dataverse 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.