“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!