Project DescriptionSynchronize 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.
RequirementsWSS 3.0 or MOSS 2007
Windows Powershell 1.0
Diskspace on local drive or network location.
Installation
- Make a directory for the files (e.g. C:\SPListSync) and copy the SPListSync.ps1 and SPListSync.xml files there
- Edit the XML configuration file with your environment parameters
- Configure the signing of your script for Windows Powershell to allow your script to run (see this article)
- Test-run the script from Windows Powershell with: C:\SPListSync\SPListSync.ps1 C:\SPListSync\SPListSync.xml
- 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)
ConfigurationAll 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
NotesThe 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:
- Go to the List settings
- Right-click on a Column-name link and choose "Copy shortcut"
- 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 LivespaceDonations are always welcome if you like the product and get something out of it. Please use the following link:
Make a donation