Flow, Dataverse and upsert

Let’s continue with the hidden gem of flow theme. That is, the features of cloud flow that you may not be aware of. This time it’s the turn of Dataverse’s upsert function.

You might already guess what’s going on. Upsert is a combination of update and insert. It’s an operation to update an existing row. If the row does not exist, one is created.

Let’s take a closer look at it.

Example – Managing Teams

We like to have a tool for managing the teams in our organization’s MS Teams. The basic information for existing teams is loaded daily into the tool (with cloud flow). Gathered data is then supplemented manually with the tool over time with additional information as needed.

Most likely, we’ll want to save at least the team’s name (Name) and unique identifier (Team GUID) so we can identify the team.

Our table looks like this.

The traditional way

A flow, which adds new teams and updates existing teams, looks like this. Basic stuff.

• List all teams
• Go through the teams one by one (Apply to each)
• Retrieve all teams from the table with the value in the TeamGUID column matching the identifier of the team in the rotation (List rows)
• If any are found, the first of them will be updated
• If none are found, one is created

After the flow run, we have a list of teams in Dataverse.

But this can be done differently.

Upsert function

Let’s start as in the previous example by listing all the teams. But after that, only the Dataverse update a row function is performed using the Team Id as the row identifier.

Ready! That’s all we need. If a match is found in Dataverse with the Team Id, it will be updated. If not, a new row is created. The new row’s identifier will be the corresponding Team Id.

So there is actually no need to maintain a Team GUID field in the Dataverse table.

The trick here is that we are using an external identifier as the row id for Dataverse. This external identifier has to be in GUID format. Of course, this is not always possible.

Delete rows

Now, adding rows and updating existing ones is really straightforward. But what is the easiest way to remove non-existing teams from the table?

Upsert (and update) always update the row. So by examining the Modified On column you can identify the teams that have been removed, as they were not updated by the flow.

At the top of the flow, we’ll use compose to create a timestamp.

After going through all the teams, we’ll retrieve the rows that were not updated during this run. They can be identified by checking if their Modified On value is earlier (or “less than”, for the purposes of the filter) than the starting time of the flow (timestamp).

These rows can then be deleted.

The flow looks like this.

Other datasources?

Does the same trick work with other datasources (SharePoint, SQL, Google Sheet, Excel…)?

No.

Even if it’s a bit limited, upsert is still a great feature.