SharePoint Client API

The SharePoint Web Client API connector is used to connect to SharePoint using the Client Object Model and can be used to work with Lists and Document Libraries.

The SharePoint Client API provider can be connected to the following editions of SharePoint.

• SharePoint 2010
• SharePoint 2013
• SharePoint 2016
• SharePoint Online (Office 365)

The Client API connector can read and write data into SharePoint Lists and Document Libraries. The connector is limited by the capabilities of the SharePoint Client API.

Connection

To connect to a List or Document Library enter the URL to your SharePoint site. Choose the authentication and connect.

Only use the base url to your site i.e. http://<sharepointurl>/<site1>/<site2>

Next choose a List or Document Library to connect to.

SharePointUrl

Specifies the URL to the SharePoint Site that contains the list.

ListName

The name of the list that the data source is connected to.

ListViewName

The view that the data source is connected to this is normally the "All Items" view. If you want Data Sync to see a subset of List Items then you can select a user defined view.

ListViewCamlQuery

You can specify a WHERE filter against the SharePoint List using the SharePoint Caml Query syntax.

<Where>
  <Eq>
    <FieldRef Name="CategoryID" />
    <Value Type="Number">1</Value>
  </Eq>
</Where>

DateTimeHandling

Specifies how DateTime values should be handled. If you use Local Timezone DateTime values then you should choose Local rather than UTC.

Choosing local causes data sync to convert SharePoint UTC values into your local timezone. When updating SharePoint local will cause your DateTime values to be converted into UTC.

DocumentLibraryUpdateBehaviour

Describes how Document Libraries are updated, by default a new file is copied into SharePoint on each ADD and UPDATE operation. If you only want to update the metadata then set this to 'MetaDataOnly` so that a new file is not uploaded.

QueryLimit

Specifies the number of SharePoint List items to return in each HTTP Request. 5000 is the maximum supported by SharePoint. Data Sync will request all list values from SharePoint in pages of this size.

TidyLookupData

Used to remove SharePoint references from lookup values so 1;#Value becomes Value. see Lookup Columns

ShowAllColumns

By default Data Sync will hide SharePoint columns that are created by default and not all that useful. Setting ShowAllColumns=True will then return all columns from the List in the Datasource Schema.

UpdateBatchSize

Specifies the number of items to send to SharePoint in each HTTP Request.

Authentication

Current User

Use the current process identity as the authenticating user.

Credentials

Use the supplied windows Username, Password and Domain to authenticate with SharePoint.

Federated (Office 365)

Use credentials collected by logging in via browser based authentication with external Office 365 authentication provider.

This method collects the Federation Cookies from a browser session and uses them to authenticate with SharePoint. Since the cookies have a short expiry of only a few days this method does not work well in a production environment since the authentication must be preformed interactively to refresh the cookies.

ID Column

The SharePoint ID column is a read-only auto number column in SharePoint data sync cannot write a user value to this field therefore this column should not be part of your Schema Map when targeting SharePoint.

If you require a column to store the ID value from your source system we suggest that you create a new SharePoint column say ItemIDand use this as the target column for the ID value from your source system.

For Data Migration you can use the same ID column values if you repeatedly run the Compare-Sync process until you get zero results.

Since eventually all the ID numbers your source data uses will be created in SharePoint and the values will eventually be in sync.

Lookup Columns

The Client API connector will attempt to connect lookup columns automatically when the related list is in the current site.

It will do this by retrieving the list values from the target list and doing a exact text match based on the supplied value and the value of the Title field in the related list.

Manual Lookup Columns

You can also manually apply the Lookup value by getting the ID number of the related item and build the standard SharePoint Lookup reference.

SharePoint uses an internal format for Lookup Values similar to 1;#Value One where the number represents the ID value of the item in the related list and the text is the SharePoint Title field of that item. To set lookup values you need to build this string and map it to the SharePoint column.

Data Sync by default will hide these column values and just extract the text value, so first you need to disable this by setting TidyLookupData=False.

TIP: Use the Data Preview feature in Data Sync to see the data that SharePoint returns.

Assuming you have added a lookup to your data source back to SharePoint to get the List ID value for the related item you can build the value like this.

IF(ISNULL(Lookup1_ID), NULL(), FORMAT("{0};#{1}", Lookup1_ID, Lookup1_Title))

Or even via in-line LOOKUPA/B functions

IF(ISNULL(LOOKUPB("ID", "MyList", WHEN("Title", myValue)), NULL(), FORMAT("{0};#{1}", LOOKUPB("ID", "MyList", WHEN("Title", myValue)), myValue))

DSID

DSID is a special column name that Data Sync uses to maintain a GUID value on a list. By creating a column called DSID on your list whenever Data Sync sees this column it will write a new Guid value back to it if it's empty. Data Sync can also write a value to this column where as it cannot update the GUID built-in column.

DSID is used in 2-Way sync scenarios where an ID value must be created on each side that is always unique.