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.
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.
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.
Does the same trick work with other datasources (SharePoint, SQL, Google Sheet, Excel…)?
Even if it’s a bit limited, upsert is still a great feature.
Interested in reading our latest Power Platform blog posts?
You’re welcome to subscribe to our email newsletter: Forward Forever Monthly. Max 1 email per month, with a curated list of latest insights and articles on Power Apps, Power Automate, Power BI.