Synchronize two SharePoint lists, without fancy addons! (via Workflows)

Scenario: You have a master list, but you don’t want everyone to see everything in it!  You want to synchronize the rows to a child list, and then apply a view with a filter of “[me]” so only a specific audience can only see their own.

Options: You could buy some fancy addons to SharePoint (that could be obsolete in future versions), or you could use the standard good old fashion work-flows to do it for you and guarantee they will work in the future!  This talks about the later.

Works With: SharePoint 2007, and SharePoint 2010 (Any versions)

Disclaimer: This process has it’s risks… TEST IT THOROUGHLY!  If not done just right, you can put your list in an infinite loop that can cause havoc on your farm… so do it SPARINGLY, and only under last resort circumstances!  BTW, I’m not responsible for your disasters! :)  OK, now with that out of the way.

Step 1.  Create the master list, and then duplicate it to a child list.  (or something similar):

Create two lists... Master and Child

In creating the lists… you have to decide if you want to a.) Just copy data one way or b.) both ways. In other words, it’s super easy to copy data from one list to the other.  But if you want to “synchronize” information between the two of them (i.e. someone makes an update in the Master list it either adds a new record, or updates an existing one and vice versa), then you need to go a little further.

Step 2. To handle the synchronization… you need to link the two lists with an “ID”.

The easiest way to do this is simple create a new column in both lists, in the Master List just call it MasterListID, and in the Child List just call it ChildListID:

Master List

IMPORTANT!  It seems “duplicative” that we are creating a MasterListID in the master list (since it already has a column called ID), but trust me… this is a handy field in terms of workflow readability down the road… just do it, trust me.

Child List

Step 3. Setup the Workflows

Easy enough, now you need two work flows.  One “Add’s or update the child list”, the other “updates the Master List”:

Create two work flows that will do the synchronization.

Step 4. Setup the first workflow: “Add or Update the Child List”

Initial settings: "Update or Add New Item in Child List"

Basic structure of the workflow.

Basic layout of the workflow

ChildList:ChildListID

Master List: ID

Condition 1: Check to see if an item already exists or not.  If it does, update it

Update Child List.

Lookup Info for MasterList: MasterListID

Note: Don’t forget to update the ChildList with the Master List ID!  You’ll need it later.

Condition 2: If the item doesn’t exist, create a new one

Create new item in Child List

Note: Don’t forget to stop your workflow after either the update or create.

If all you really needed was one way synchronization, you could stop here… but if you need it both ways, read on.

Step 5. Setup the second workflow: “Update the Master List”

Basic structure of the workflow:

Update the Master List

Condition 1 (the only condition): Check to see if it exists, if it does update it.

DANGER! PAY CLOSE ATTENTION TO THIS CONDITION!

Check EACH FIELD to make sure they have actually changed!  IF YOU DON’T, you will find your workflow constantly updating itself between the two lists, i.e. infinite loop.  So check that they are actually different:

COMPARE CHANGES IN THE COLUMNS! IMPORTANT!

Alternative:

You could in this step instead of comparing all the columns, PAUSE the workflow for a couple minutes… and then check the “Modified Date”.  If the dates are not the same then you know it changed.  But the aforementioned method is about the most full proof way to handle it.

And that’s pretty much it!  Follow those steps closely and you have achieved syncronization between your two lists.

Master List - New Item!

New Item Flows to Child List via Workflow!

If you update the child list... it flows BACK to the master!


  • http://www.hexanes.com Matthew Workman

    And of course… IF YOU WANTED a fancy addon, you might try this:

    http://www.sharepointboost.com/list-sync.html

    Anyone have any experience with that control? It looks like it does the same thing, but haven’t tried it.

  • Logan Stern

    What happens when you delete an item from one of the lists?

  • CW

    I’m using Share Point designer 2010.
    I’m trying to create these workflows for synching data from multiple Child Calendar lists to one Master Calendar list.

    End State when a new or updated item from any of the child lists is nominated to the Master list the Master list is synch with all child lists AND if changes are made on the Master list then it is reflected on the appropriate Child list

    Can you Please tell me the steps for Designer 2010 and when you create the ID columns the characteristics required in SharePoint 2010?

    Thanks
    CW

  • Noname

    That was super helpful…thanks a million!

  • Jill Riesenberg

    You are missing instructions for the step when creating new item to Update item in Master List. Can you please provide that instruction? When will you update your instructions for SharePoint 2010 workflow? Thanks!