Synchronize two data sources with Power Automate

Over the last few weeks I’ve been asked a number of times how to setup a flow to synchronize two data sources. In this post a data synchronization pattern that can be easily reused.

Some of the basic understandings

In my examples I will use two identical SharePoint lists, however most often when you synchronize two data sources you will be looking at two different systems. These systems could be SharePoint, but often you will find other datasources like SQL Server, Oracle, or applications that we have connectors for like helpdesk systems, ERP systems, CRM or whatever you may be using.

Simulated systems

I’m using List A and List B in SharePoint.

So in short, When an item is created or updated in either of these lists then we want to create or update the matching item in the other list.

Create a key

First of all we will need something that is unique in each of the lists so that we can identify the items, a key. This key should never change and be unique within the list.

Then we will need something that links the items in List A with the items in List B.

To implement the overall process try and keep the number of flows to the minimum.

Then finally when you implement a pattern like this take small steps. Don’t aim for the end goal of “synchronize two data sources” in one go. In this post I will use the small steps get there fast approach!

Step 1 – When an item is added to a data source create it in the other data source

To implement the first step, we could create a flow as shown below. A trigger one each of the two lists in two separate flos and then a step that creates an item in the other list.

But when we create a second flow to create items in List A then as an item is added to list B, we will have a lot of items created quite quickly as both flows will trigger each other. We need to put some logic into our flows to avoid that situation.

Step 2 – Check the existence of the item

In our case Title is the data and each item in the list will have an ID that is our key. in reality you will probably have more fields to update.

How can we check if the item has already been created. We could add an additional field to both lists called “Other list’s ID” or Foreign Key in database terms but that would mean that we change our existing data model. For SharePoint lists that may be ok, but if you have other business application that might not be so easy.

I’m going to introduce a Mappings list.

This list has two fields List A ID and List B ID. Tjhis way we can keep track of the different IDs in each of the datasources/lists.

So, now we can update our flow to create mapping items.

creating the mappings doesn’t solve our problem of both lists triggering items in the other lists forever yet. So, before we create an item in the ‘other’ list we will need to check if the item triggering the flow already exists in our mapping list.

Using the Filter query in the get items action we can select the item that we want. All we now have to do is add another condition step to our flow that checks the number of items returned by the get items.

And as one flow now triggers the other flow, we will see that items created by our two flows aren’t triggering any more updates, while our flows still use the When an item is created or modified trigger.

Ok, that’s good. The creation of an item triggers the creation off an item within the other list. But how about updates? when items are updated we do want changes to come through to the other list.

Step 3 – Synchronize two data sources and implementing updates

When an update has happened to our data we will already have an item in our mapping list. So the final step now is easy! (Famous last words!)

The expression used to get the ID is using the first, to avoid an apply to each being added by Power Automate:

first(outputs('Get_items')?['body/value']).ID

The above flow, where we just added an update item to our yes branch, will generate many updates again. It is simply not that easy!

So how can we avoid this? I’ve seen people add update dates and all sorts of other things to keep track of changes or an initial source of the data to avoid these kind of spinning flows, but the solution is simple. Don’t update if there is nothing to update..

In short first check the values of the item that you want to update with the values that you are using to update the item with.

The same expression for the ID of the item is used in the get item action as well:

first(outputs('Get_items')?['body/value']).ID

So in this post, you saw 3 small steps to create 1 solution. Quite often solving multiple small problems to resolve the larger problems make it a lot easier to get to your end goal.