Project Description
Synchronize information with other lists or SQL Server table based on a linked column. This can be helpfull when having list with companies and another list with contacts. The company-information (e.g. Business phone and address) can be copied to the linked contacts.


Requirements
WSS 3.0 or MOSS 2007
Windows Powershell 1.0
Diskspace on local drive or network location.

Installation
  1. Make a directory for the files (e.g. C:\SPListSync) and copy the SPListSync.ps1 and SPListSync.xml files there
  2. Edit the XML configuration file with your environment parameters
  3. Configure the signing of your script for Windows Powershell to allow your script to run (see this article)
  4. Test-run the script from Windows Powershell with: C:\SPListSync\SPListSync.ps1 C:\SPListSync\SPListSync.xml
  5. Schedule the script in Windows Scheduled Tasks
(See screenshots of configuring the lists for the samle XML file on my blogpost Sharepoint list-synchronization with Powershell 1.0)

Configuration
All configuration is done through the XML-file.

The example configuration is syncronizing company information on contacts.
URL: http://intranet.domain.local
Lists: Companies, Contacts
Columns created: A lookup column in Contacts called "Company Link" returns the Company-column from the list Companies
Copies data from/to the columns: Company, WorkAddress, WorkZip, WorkCity, WorkCountry, WorkPhone

You can add as many sites you want in the XML-file if they have unique IDs. The sample has one site defined.

XML Parameters (version 1.1)
siteurl: URL of the site
webname: Name of the webfolder
ParentSQLServer: Name/IP of the SQL Server if the parent-data is copied from a SQL DB table. (Leave this blank if you copy from a Sharepoint list)
ParentSQLDatabase: Name of the SQL Database. (Leave this blank if you copy from a Sharepoint list)
ParentSQLStatement: The SELECT-statement, the name of the parentlinkcolumn must be selected also. (Leave this blank if you copy from a Sharepoint list)
parentlistname: Name of the list containing the parant information needed on the child-object in the childlistname. (Leave this blank if you copy from a SQL Server)
parentlinkcolumn: The system-name of the column linked to by the childlist
childlistname: Name of the list that is linked to the parentlistname
childlinkcolumn: The system-name of the column linked to the parentlist parentlinkcolumn
CreateNewChilds: Create new items if they do not exist in the childlist (0 for NO and 1 for YES)
SyncColumn: Define these with unique IDs. One for each synchronized column
SyncColumn-ParentColumn: The system-name of the column in the parentlist to be copied from
SyncColumn-ChildColumn: The system-name of the column in the childlist to be copied to

Notes
The powershell script can be scheduled using Windows Scheduler with the following command:
%windir%\system32\WindowsPowerShell\v1.0\powershell.exe -command "& C:\SPListSync\SPListSync.ps1 'C:\SPListSync\SPListSync.xml'

The system-name of a column is found in several ways, but a quick one is:
  1. Go to the List settings
  2. Right-click on a Column-name link and choose "Copy shortcut"
  3. Paste the information into Notepad and see the system-name at the end of the line

Known issues
  • The script can only run locally on Sharepoint servers.
  • The SQL statement cannot include the following chars: < >

Visit my blog for more technical Sharepoint articles: Jesper M. Christense Livespace

Donations are always welcome if you like the product and get something out of it. Please use the following link: Make a donation

Last edited Sep 18, 2008 at 10:22 AM by JesperMChristense, version 9