“I’d like to synchronise all the changes in a SharePoint lists listA in a listB, what would such Power Automate flow look like?”
One of the missing features of SharePoint is that it’s impossible to set permissions on a column. You can set a unique permission on each item, but not on a specific column in that item. If you need to do that, e.g. to keep basic user information available while restricting access to his salary, you’ll need a workaround. A solution that often consists of two SharePoint lists. One list with all the public data, and another list with restricted access that extends the data by the extra columns.
But to make it work you’ll need also a flow. A flow that would synchronise all the changes from the first list in the second list. If there’s a new item, create a new item, if there’s an update, update the corresponding item, and if an item is deleted, mark it as deleted in the second list (or delete it). What such flows would look like will be the topic of this post.
Prerequisite: the second list should have a column called ‘OriginalID’ to easily connect the items.
Create or update existing item
The solution consists of two flows, one for the new/updated items, and one for the deleted items.
The flow to process new items and update the existing ones will be the same flow as when checking if SharePoint item already exists. Take the ID of the item that triggered the flow, and do a lookup on the second list. This is the place where you’ll use the ‘OriginalID’ to connect the items. If there’s no item with such id, create new item. Otherwise use the returned ID to update the existing one.
OriginalID eq '<current item ID>'Note: < … > is a placeholder, replace it including the < and >.
Don’t forget to set the ‘OriginalID’ column when creating the new item (with the ID from the trigger output)!
Delete or mark deleted items
The first flow took care of the new/update items, now it’s time to process also the deleted ones. This will need another flow with the trigger ‘When an item is deleted’. It’ll be very similar to the first one, just with a different trigger. Once an item is deleted, search for the corresponding item in the second list as before. If it finds the item (and it should always find the item if the flows work), you can either delete it, or update some column, e.g. status to DELETED to keep the information.
Summary
When you synchronise two SharePoint lists, you should consider using two Power Automate flows. One flow to handle the new and updated items, and a second one, often forgotten, to handle also the deleted ones. The flows themselves are then very simple as you could see in this article.
Excelent! Thansk for sharing this!
How to achieve these things using scheduled flows? When I try that, one ‘apply to each’ action is getting created in the other ‘apply to each’ action which is creating duplicate items in the target list. I recently started learning power automate. Could anyone help me please? Thanks in advance! 🙂
Hello Srilaxmi,
my guess would be that you’re having some multiple selection column in the list, e.g. people picker, choice, lookup… If you have a multiple selection column then flow wants to process each selection separately in a loop = it’s adding an extra loop. Such columns must be preprocessed, e.g. https://tomriha.com/how-to-update-multiple-people-or-group-column-in-power-automate/ or https://tomriha.com/what-value-to-use-to-input-entire-array-in-power-automate/.
@Srilaxmi, unless there is a desperate Need to keep the two lists Synchronous in Real-Time, you can think of a time-based trigger: run the task every day or twice a day or so on (you can choose the periodicity). Compare each item on modified date, and whichever one has a more recent update date-time stamp, feed that into the other list. Alternately (Slower performance, and depending on number of columns in metadata), you compare field-on-field, using “Get Changes of an Item or a File (properties only)”. This should work for most cases, unless you have two lists popular enough to have Many-many changes every minute.
I’m trying to sync files and folders from a Teams/ Sharepoint folder in one organisation, where I am an owner, to a Sharepoint folder belonging to another organisation, where I am a guest.
Basically it refuses to take the latter site address. Asks me to pick one “from the dropdown”. Is this not possible at all?
Hello Vinay,
I believe that the standard M365 actions are restricted to the tenant where the Power Platform environment is located. To connect to another tenant you’d need an Azure registered application and the general HTTP request action to do the operations using HTTP requests through the application.
I’m looking to simply copy items over from one list to another when they’re added or modified, so I only followed the first half of the instructions. My flow triggers as expected and shows as running successfully, but the data never appears in the new list. In the create and update actions, I’ve tried adding dynamic content from both the source and the target and neither seems to work. It always runs successfully but no data appears in the second list.
Hello Jake,
that’s something you’ll have to check in the flow run history.
– how is the ‘Condition’ evaluated, is it correct? Does it go into the ‘Create item’ or ‘Update item’ branch?
– does the action happen or is it grey/light green?
– do you have some multiple selection field in the lists (is there an extra ‘Apply to each’)? Does it have any value?
Hello Tom,
I’m having the same issue has Jake. For me the Action “Create item” is light green. I can’t view the contents of it.
Trish
Hello Patricia,
light green means that the action was skipped, in your case it probably went into the If No branch of the condition.
I had the same issue as Jake, trying to troubleshoot but no look so far. Appears to succeed in run history but no data beyond the ID from the original new item is transferred.
*luck
Hello Alex,
try to debug the flow – add a few ‘Compose’ actions in it, enter the dynamic contents you want to track in these ‘Compose’ actions, and then you can track in the flow run history what’s happening and if some of these actions contains a different value than you’d expect.
Hi, thanks for sharing this great tips. However, I have followed the steps in my flow but couldn’t save the flow as it turned me with “Flow save failed with code ‘InvalidTemplate’ and message ‘The template validation failed: ‘The action(s) ‘Get items’ referenced by ‘inputs’ in action ‘Condition’ are not defined in the template.’.’.”
Could you please help me to fix this? Thanks in advance!
Hello Helen,
the action is telling you that you’re using output of action ‘Get items’ but such action doesn’t exist in your flow. This often happens when you build an expression, use the ‘Get items’ output, and then rename the action. You should either return to the original name or check where you’re using the output and rename it according to the new action name.
Hi Tom, my flow is forcing “If yes -> Create item” into an “Apply to each” loop once I’ve added “OriginalID,” “Title,” or any other value from “Get items”; I have yet to complete/see effect of this, but how does yours not force this?
I was struggling as well and then I realized that under “Create Item” I was using dynamic content from “Get Items” step instead of “When an item is created or modified”!
RE: forced “Apply to each loop”,
“`
The execution of template action ‘Apply_to_each’ failed: the result of the evaluation of ‘foreach’ expression ‘@triggerOutputs()?[‘body/ID’]’ is of type ‘Integer’. The result must be a valid array.
“`
–in setting the original trigger’s “ID” field to the “Apply to each,” as I don’t know what else would be logical to go in this forced-field, flow fails upon test trigger
Hello Brandon,
as already said in the comments, you must reference the flow trigger when creating the item, you use output from ‘Get items’ only for updates.
Thanks for sharing this!
Would appreciate some troubleshooting pn the implementation; the Update and Delete functions are working great, but my Create function doesn’t work. Any thoughts on what I did wrong?
Thanks!
I figured it out.. I had created an apply to each loop just before the create list item function.. working now!
Thanks Tom for the suggestion. I had a request to have items that were added to the out-of-the-box special Event list on a SharePoint site, and have those items flood over and stay updated in a regular SharePoint List. So it’s not just a simple list to list sync. But amazingly I was able to model a process after this one to get it to work. Thank you for sharing your expertise!