“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/.
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?