You can connect a SharePoint list with another SharePoint list (at the same or at another installation) to pull the data and keep up-to-date with background update as follows:
1. Select the Layer2 SharePoint List Provider as driver.
It is automatically provided with the BDLC installation. It is required only at the destination server. The source system is connected via web services. SharePoint 2007 is supported as well as 2010.
2. Enter connection string as follows:
- Web- and View-GUID based:
WebSiteUrl=https://myserver/;List=ListName;ViewId={ViewId};Login=domain\name;Password=pwd
Url=http://myserver:80/Lists/mysourcelist/AllItems.aspx;Login=domain\name;Password=pwd
- or with current credentials:
Url=http://myserver/Lists/mylist/AllItems.aspx
Url=http://myserver/Lists/mylist/AllItems.aspx?View={7c989618-db65-46ac-9c3b-bd33c222883a}&SortField=Testcol1&SortDir=Descc
- Url or WebSiteUrl specifies the adress of the SharePoint source web site and can contain the complete path to the list.
- List optionally specifies the name or ID (GUID) of the view in the source list. Guids must not be decoded.
- Login specifies the user login, optionally including domain. If not present, the credentials of the currently logged in user are used. Please take cate in case of background updates (see below).
- Password specifies the user password.
3. SQL queries
SQL queries are not supported as this time. Create views in the source system to filter data. Use calculated fields in the source systems to simulate "aliases" for field names in SQL queries to fix mapping issues.
4. Primary key
Set ID as primary key (as pre-filled).
5. Add columns
The columns of the source list are created automatically in the destination list, if not already available.
6. Check Mapping
Columns are mapped automatically by internal column name.
7. Background Update
If background update is planned, please enter user login and password or make sure, that the service account for the timer job has the rights to read your source list.
8. Write-back
Write-back of changes in the destination list to the source list is not supported yet for SharePoint-to-SharePoint connections in the current version.
Fig.: Setup of a SharePoint-to-SharePoint list connection in the list settings dialog with BDLC.
Please note:
- Column types, names and settings of the source list are used to create the structure of the destination list.
- Columns of type managed Metadata, Lookup, User/Group are not fully supported and can be transferred as text-only. You can delete the auto-created columns and replace by the required column type. Please note, that the result of transferred lookup and user / group column content can be undefined, if the context (lookup list, user, group) is not available or has different IDs within the target system.
- Calculated columns are supported as data source.
- Column restrictions, descriptions, formatting, default settings, length etc. are taken from source list
- There is an issue with the preview display on SharePoint 2007 list data source. Additional internal columns are shown.
- Be careful with with special characters in internal field names of the source list. Sometimes there are issues with the SharePoint web service to retrieve data for those type of columns.
Please take care about the primary key:
When selecting the Layer2 SharePoint Provider, ID is set as primary key automatically and the column "bdlcID" is created in the destination list and mapped to host the source ID values.
Known Issues:
- Error Message: "List structure could not be created. A column named 'Title' already belongs to this DataTable." Workaround: If you are referencing a view in your connection string, do not include the title field in that view. The connector maps the title field by default, and attempting to add a second mapping via the view appears to be the cause of this error. That is an issue with the provider and will be changed with the next version.