Copy data between two SharePoint lists (or any two data sources) efficiently

One common scenario in Microsoft Flow is copying data between two different data sources. For example, if you have a SharePoint list in one site, and you want all the items to be copied over to a second SharePoint site, you can build a flow to do that. This post will walk you through how to do this efficiently as possible.

In this scenario there are two lists SharePoint lists (but the same process would work between a SharePoint list and a SQL table or any number of the 100+ services supported by Microsoft Flow). It's also not required that the names of all of the columns are the same, but you will need to be able to populate all of the required columns.

Finally, there has to be at least one column that will be able to uniquely identify items between the two SharePoint lists. In the example below, we will assume that the Title column is the same, but it could be any column you want.

Setting up the trigger

The first step is to set up your trigger on the source SharePoint list. Because you want to get all item changes (not just new items) you should select the Sharepoint – When an existing item is modified trigger. 

Note that despite the name, this will trigger for both when an item is added or when it's modified in the list.

Finding the item in the destination

Next, you will need to search for the item in the destination list, to get its ID to update it. Although Microsoft Flow does have a Filter action, you should not use that in this scenario because that will download all of the list items from SharePoint, which will be slow, use up your quota, and simply will not work if the list has more than 256 items.

Instead, you should use the Filter Query field on the Get items step in SharePoint. Add the Get items action and select the Show advanced options to show all the fields. If you are matching the Titles of the rows of the source then you want to type: Title eq '[select the title from dynamic content]' … (be sure to include the single quote characters):

You can see the example above. You don't necessarily need a strict 1:1 column mapping between the two data sources, for example, if you have columns for First Name and Last Name in the source list, and a column for Full name in the destination list, you could type: FullName eq '[First name] [Last name]'

Adding a condition to check if the item exists

When you get the items from the destination list, there are two possibilities:

1. The item doesn't yet exist in the destination, so you need to create it
2. The item already exists in the destination, and you need to update it

You will use a condition to tell the difference between these two. Select New step and then Add a condition. On the condition card, underneath the left hand box, select Add dynamic content. You will find the collection that contains the list of all the items returned from Get items, it is called value. Be sure to select the value from Get items (not from the trigger When an existing item is modified).

In the relationship select is equal to, and in the value type 0. Now, you will need to go to advanced mode and add the length function. This is important because the value dynamic content returns back the list of items — and you want to check the length of the list and check if that equals 0 (not if the whole list is 0).

In the advanced mode, type length() around the body('Get_items')?['value'] expression. It will look like:

Create the item

In the If Yes branch, you will add in SharePoint's Create item step. Select the site and list you use above in Get items. Inside the Create item, you should populate each of the columns with fields from the trigger only. You should not use any data from the Get items step because that would be from the destination list (not the source list). Get items will appear above the trigger, so be sure to scroll to the bottom.

Update the item

In the If No branch, you will add SharePoint's Update item step. Again, select the site and list. Next, you will need to select the Id returned from the Get items step. 

Note that when you add the ID, you will get an Apply to each container automatically added around the Update item step. This is expected — if the query you used in the Get items step is accurate, it will update only the item you wanted to copy. After you fill out the remaining fields (again, be sure to use the outputs from the trigger, not from the Get items call), your condition block will look like this:

Other limitations and workarounds

This flow will ensure that whenever an item is added or updated in the first list, that update will be reflected in the second. One limitation with this process is if items are deleted from the first list they will not be deleted from the second. The reason for this is there is no trigger for When an item is deleted, thus, there is no way for a flow to be notified when a deletion occurs. Instead, we would recommend that instead of deleting items from your SharePoint lists (or SQL tables, or other data source), you add a column that indicates that the item is no longer needed or relevant. That column will sync between the two lists.

Another limitation is if the field that you are using to keep items in sync between the two lists changes, a new item will be created in the destination list. For example, if you are using a person's Name to copy between the two lists, and their Name changes, the flow will think that it is a new item, not an update to an existing item. If you can guarantee that the Name will never change then you won't be impacted by this. However, if Name is a field that can change, you will need to add a new column to the destination list that stores the ID of the item in the first list. In this case, rather than using Name to find items, you can use ID instead (and the ID is guaranteed to always be unique).

Finally, as you may have noticed, this is not a two-way sync. That means that if items are updated in the destination list, those changes will not be reflected in the source list. You should not attempt to set up two-way sync with the above flow because that will result in an infinite loop without additional modifications (e.g. list A will update list B, which will update list A, which will update list B, etc…).

The above limitations aside, this is a relatively easy way to have one list always be updated with changes from another list. I hope that you found this helpful, please ask questions in the community if you run in to any issues with this type of a flow.