Setting Up SharePoint List Synchronization

March 16, 2014 , by

Pistachio supports two-way synchronization between SharePoint Lists and tables the SQL databases it works with. Use this feature to attach a workflow to data in your SQL database, or simply to surface SQL data in a SharePoint list.

With list synchronization, a corresponding item will be added in the SharePoint list every time a new record is added in Pistachio. When a record is edited, the corresponding list Item will be updated as well. Also, items that are updated in SharePoint are synchronized back back to the SQL table.

Create the SharePoint List

First, create a SharePoint List that utilizes the PistachioSyncedList content type that was installed with the Pistachio web part, and make it the default content type for the list. You may also create a content type that is derived from the PistachioSyncedList and attach that to your list. The list must be located in the same SharePoint site the Pistachio Web Part is utilized in.

Add one column to the list for every column in the SQL table that you want replicated into SharePoint. The column names don't need to match since you'll match them up in the Pistachio admin portal.

Make sure the two required content type columns appear in your list: PistachioID and PistachioRecordID. These fields are used by Pistachio to link list items to SQL records.

Update the Table's Data Model

After you've created the SharePoint List, back in the Pistachio admin portal specify the name of the SharePoint List in theRecords tab of the Pistachio table to synchronize.

Then, in the field properties screen for each field in the table you want synchronized, enter the name of the SharePoint list column that will mirror its data.

Create a Record Ownership View

Pistachio uses its record ownership table (Record_Owner) to track the synchronization of records between a SQL table and SharePoint list. You need to create a record ownership view within each SQL database that has tables that are synchronized. The view has the following definition:

SELECT * FROM PistachioConfig.dbo.Record_Owner

You only need one view per database, regardless if more than one table is being synchronized. Also, PistachioConfig in the SQL statement above is the default name of the Pistachio configuration database. If you changed the name during installation, update the statement with the current database name.

To test, add a record with Pistachio to the table being synchronized. A corresponding SharePoint list Item should be created. When a record is edited, the corresponding item will be updated as well. Then, try editing the item in SharePoint and check that the change was be synchronized back to the SQL table.