Synchronize SharePoint Lists (Calendar/Tasks) Using the Workflow Power Pack

When I worked in the SharePoint team for a London based financial firm, one of the first user requests that came up was to find an easy way to synchronise team calendar entries with a department level calendar.

There are a number of ways to approach the automatic synchronisation of lists (a Calendar is just a SharePoint list). In this posting I will discuss how to synchronise any source list with a destination list, with the Exception of Document Libraries, using the Muhimbi Workflow Power Pack and a small script.

A quick introduction for those not familiar with the product: The Muhimbi Workflow Power Pack for SharePoint allows custom C# or VB.NET code to be embedded in SharePoint Designer Workflows without the need to resort to complex Visual Studio based workflows, the development of bespoke Workflow Activities or long development cycles.

Before developing the script we have to think about our approach as the synchronisation of list items involves both adding new items as well as updating existing ones. In order to be able to update an item we need to somehow track which item in the destination list is a copy of an item in the source list. As SharePoint does not provide an easy way to track this kind of information (The CopySource field is read only, sigh) the script will need to create a new and hidden field on the destination list to track an item’s origin.

The solution presented below allows any two lists to be synchronised in one direction (Source List to Destination List). The name of the Destination List is passed using Parameter 1. Only those fields available in both the source and destination lists will be copied over so there is no need for the two lists to have exactly the same content type. The very first time the script is executed, a new hidden field will be created on the Destination List, so make sure you execute the first run using an account that has the appropriate privileges to add columns to the list.

Create the workflow as follows:

1. Download and install the Muhimbi Workflow Power Pack for SharePoint.

2. Make sure you have the appropriate privileges to create workflows on a site collection.

3. Create a source and destination list, e.g. Calendar and Destination Calendar.

4. Create a new workflow using SharePoint Designer.

5. On the Workflow definition screen associate the workflow with the source list and tick the two automatic boxes and proceed to the next screen.

6. Click the Actions button and insert the Execute Custom Code action .

7. Click parameter 1 and enter the name of the Destination List.

8. Insert the following C# based code by clicking this code.

   SPList sourceList = MyWorkflow.List;
   
   SPList destinationList = MyWorkflow.Web.Lists[MyWorkflow.Parameter1 as String];
   
   SPListItem sourceItem = MyWorkflow.Item;
   
   // ** Check if our custom Source field already exists on the destination list
   
   if (destinationList.Fields.ContainsField("_M_CopySource") == false)
   
   {
   
         SPField newField = destinationList.Fields.CreateNewField("Text", "_M_CopySource");
   
         newField.Hidden = true;
   
         destinationList.Fields.Add(newField);
   
   }
   
   // ** Check if the item needs to be copied or updated.
   
   string camlQuery = "<Where>" +
   
                      "<Eq><FieldRef Name='_M_CopySource'/><Value Type='Text'>{0}</Value></Eq>" +
   
                      "</Where>";
   
   camlQuery = string.Format(camlQuery, sourceItem["FileRef"]);
   
   SPQuery query = new SPQuery();
   
   query.Query = camlQuery;
   
   query.RowLimit = 1;
   
   // ** Query the list
   
   SPListItemCollection items = destinationList.GetItems(query);
   
   SPListItem newItem = null;
   
   if (items.Count == 0)
   
       newItem = destinationList.Items.Add();
   
   else
   
       newItem = items[0];
   
   // ** Copy the fields
   
   foreach(SPField field in sourceItem.Fields)
   
   {
   
       if (newItem.Fields.ContainsField(field.InternalName) == true &&
   
           field.ReadOnlyField == false && field.InternalName != "Attachments")
   
       {
   
          newItem[field.InternalName] = sourceItem[field.InternalName];
   
       }
   
   }
   
   // ** Delete any existing attachments in the target item
   
   for (int i = newItem.Attachments.Count; i > 0; i-- )
   
   {
   
       newItem.Attachments.Delete(newItem.Attachments[i-1]);
   
   }
   
   // ** Copy any attachments
   
   foreach (string fileName in sourceItem.Attachments)
   
   {
   
       SPFile file = sourceItem.ParentList.ParentWeb.GetFile(sourceItem.Attachments.UrlPrefix +
   
       byte[] imageData = file.OpenBinary();
   
       newItem.Attachments.Add(fileName, imageData);
   
   }
   
   // ** Remember where the original was copied from so we can update it in the future
   
   newItem["_M_CopySource"] = sourceItem["FileRef"];
   
   newItem.Update();

9. Close the Workflow Designer and add an item to the source list to trigger the workflow.

10. Once the workflow has finished, open the destination list and verify the source item has been copied over.

    

As mentioned previously, it should be possible to use this script on any list with the exception of Document Libraries. Synchronising Document Libraries can be done using a similar approach, but copying an item works slightly different.

The script in this post assumes the destination list lives in the same site as the source list. This, however, can be easily changed by referencing the desired list in line 2 of the code.

You may want to add a condition to only synchronise list items that match certain criteria (e.g. status = completed).