Layer2 Cloud Connector  V5 for Office 365 and SharePoint

​​The Layer2 Cloud Connector for SharePoint can be used to connect almost any external data sources to native SharePoint lists and libraries, for example via the OData protocol as used by many applications, e.g. SAP or Microsoft CRM/ERP systems. This FAQ gives the full specifications of the Layer2 Data Provider for OData that is fully included in the product.

Layer2 Data Provider for OData: Specifications for Cloud Connector

The Layer2 Data Provider for OData can connect data sources via the OData protocol. The Open Data Protocol (OData) is a Web protocol for querying and updating data via web services. OData is being used to expose and access information from a variety of sources including, but not limited to, relational databases, file systems, content management systems and traditional Web sites.
 
SharePoint-Integration-OData.png

 Fig. You can use the OData.org demo services to verify the connector.

 
Normally, an OData request is presented in a single URL. Layer2 OData provider splits the select portion and the data collection URL portion to present them as a connection string and a separate select or query statement.

Connection String:
  • URL

    This is the Service Root URL for the OData source. The service root URL identifies the root of an OData service. The resource identified by this URL MUST be an AtomPub Service Document (as specified in [RFC5023]) and follow the OData conventions for AtomPub Service Documents (or an alternate representation of an Atom Service Document, if a different format is requested).
    OData: JSON Format specifies such an alternate JSON-based representation of a service document. The service document is required to be returned from the root of an OData service to provide clients with a simple mechanism to enumerate all of the collections of resources available for the data service. This information is mandatory.
  • Collection

    The resource path section of an URL identifies the resource to be interacted with (such as Customers, a single Customer, Orders related to Customers in London, etc.). The resource path enables any aspect of the data model (collections of entities, a single entity, properties, Links, service operations, etc.) exposed by an OData service to be addressed.
  • Authentication

    This setting specifies how the Cloud Connector authenticates against the OData source. It can be one of the following:

    Anonymous
    This is the default value and used to connect anonymous resources.

    IECookie
    If an authentication cookie has been created using the cookie manager, this authentication method can be configured to authenticate by using the authentication cookies. (See The Cookie Manager for more details)

    Windows
    You can use Windows AD authentication to login. Please provide user name and password as well, e.g. "username=myDomain\myUserName; password=myPassword".

    SharePoint_FBA
    The SharePoint Form Based Authentication (FBA) with username and password parameters required.


    These methods are Dynamics CRM-specific for connecting to their OData service:

    DynamicsCrmOnline
    For connecting to Microsoft Dynamics CRM Online. Username and password parameters required.

    DynamicsCrmADFS
    For connecting to an Microsoft Dynamics CRM Online instance that uses ADFS. Username, Onlineuser, and password parametesr required. For example: "username=myDomain\myUserName; [email protected]; password=myPassword;"

    NextCRMOnline
    Custom authentication for use with the NextCRM system, but also works with many other Online CRM providers (online and on-premises). Username and password parameters required.

    AdfsOnPremiseNTLM
    For use with systems that have ADFS on-premises authentications that require an NTLM handshake to happen. Username and password parameters required.

 

 

See below for connection string samples.

Select Statement:

A Select statement for the Layer2 Data Provider for OData refers to the query options section of an OData URL which is specified to control the amount and order of the data requested. All OData services MUST follow the query string parsing and construction rules defined in OData documentation section and its subsections. Some services, like Dynamics CRM, do not support all type of queries (see the system-specific documentation about their OData service for more information). See below for examples.

Layer2 Data Provider for OData: Sample Connection Strings

  • Url=http://services.odata.org/V2/Northwind/Northwind.svc;Collection=Products;Authentication=Anonymous

Layer2 Data Provider for OData: Sample Select Statements

  • In case no data query is given, the whole collection is returned as given in the connection string.
  • ​Enter $select=* to select all fields without any condition.
  • Field Set: Enter $select=myField1, myField2 to select the data fields myField1 and myField2 only. Please note that field names are case sensitive (depending on OData source implementation). It is strongly recommended to remove unwanted fields from query to improve performance.
  • For quick data preview performance while testing, you can use the $top=<value> parameter to limit the number of records pulled. For example, $select=myField1, myField2&$top=10 will only get the first 10 records. Make sure to remove the top filter once you are ready to sync the data.
  • Filter: Enter $select=ProductID, ProductName&$filter=ProductID eq 3 to filter for specific ProductID. You can use use eq operator for filtering. OData protocol enables you to use other relational operators such as not equal (ne), less than (lt), less or equal (le), greater than (gt), greater or equal (ge).
  • Logical Operators: Enter $select=ProductID, ProductName&$filter=(ProductID gt 3) or (ProductID eq 1) to form logical expressions with and, or, not and brackets.
  • Arithmetical operations: You can apply standard operators to add (add), subtract (sub), multiply (mul), divide (div), or find remainder (mod): $select=ProductID, ProductName&$filter=(UnitPrice mul UnitsInStock) lt 45 and UnitsInStock ne 0.
  • Numerical functions: If your properties are numbers you can apply floor, ceiling, and round functions. Example of the query that uses these functions is $select=ProductID, ProductName&$filter=floor(Price) eq 3 or ceiling(Price) eq 3.
  • String functions: There are a lot of string functions you can use in your filter expressions like this:
    length(string): $select=ProductID, ProductName&$filter=length(ProductName) lt 7.
    trim(string), toupper(string), tolower(string): $select=ProductID, ProductName&$filter=toupper(ProductName) eq 'CHAI'.
    substringof(‘part’, text): $select=ProductID, ProductName&$filter=substringof('Chai',ProductName).
    endswith(text, ‘part’): $select=ProductID, ProductName&$filter=endswith(ProductName,'Chai').
    startswith(text, ‘part’): $select=ProductID, ProductName&$filter=startswith(ProductName,'Chai').
  • Date / Time Functions: If you have datetime properties in the resources you can use several date part functions such as year(), month(), day(), hour(), minute(), and second(). As an example: $select=ProductID, ProductName&$filter=year(OrderDate) eq 1996.
  • Sort Order Functions: You can order results by some property or function using the $orderby query option. Default order is ascending but you can change it. Some examples are:
    $select=ProductID, ProductName&$orderby=ProductID desc
    $select=ProductID, ProductName&$orderby=length(ProductName)

Please read more about OData query options here:
http://www.odata.org/documentation/uri-conventions

Layer2 Data Provider for OData: Step-by-Step introduction

You can simply configure your OData connection in the Layer2 Cloud Connector Connection Manager.


Fig. Example connection string and select statement to connect to Microsoft CRM via OData.

A step-by-step introduction how to setup the Layer2 Cloud Connector for SharePoint using the Layer2 Data Provider for OData to connect to Microsoft CRM Online is given here.

Layer2 Data Provider for OData: Use Cases & Known Issues

  • Many applications today can expose data via OData web requests. The provider can be used to connect to this type of data entity and replicate to native SharePoint lists, e.g. SharePoint 2010/2013 on-premises or in the cloud (SharePoint Online/Office 365).
  • Please note that the Cloud Connector modifies only changed data in case of modified OData results - not all items in list. Change notifications, alerts and workflows can be used as usual. The content of additional unmapped columns are kept while updating items in SharePoint.
  • You can use the Professional Edition of the Cloud Connector to sync OData sources with almost any other data entities, e.g. SQL, OData, etc. (Not just with SharePoint!)
  • Please note that this provider can work bi-directionally, if supported by the target system's OData service. It can write-back changes, as done in a SharePoint List or library (or an offline copy of that, like with Outlook).
  • The Cloud Connector saves a cache of the metadata from the OData service for performance reasons. This can cause issues if the cache is old; for example, a newly added field to the OData service is not selectable in the Connector (as it's not present in the cache). You can fix this by going to C:\ProgramData\Layer2 Cloud Connector\Metadata, finding the .metadata file with the name of your OData service, and deleting it. The next sync or authentication to that service will cause a new version of the cache to be created.

Layer2 Data Provider for OData: Next Steps

Register, download and evaluate the free shareware edition of the Cloud Connector today here.
Share this site on Xing
Share this site on Google+
Share this site on LinkedIn

The software works perfect and does just what we need.

Contact
Frank Daske

Frank Daske

Business Development Manager
+49 (0) 40 28 41 12 - 30
[email protected]

Please note that the 011 exit code must be dialed first for all international calls made from the USA, Canada and Australia.

Related News

New Microsoft Teams Connectors: Layer2 adds 100+ typically used backend systems

​The Layer2 Cloud Connector is now able to connect to Microsoft Teams to quickly integrate more than 100 IT backend systems including local SQL, ERP/CRM.

Layer2 Office 365 and SharePoint integration now compatible to FIPS

The newest Layer2 Cloud Connector release is compatible to the Federal Information Processing Standards (FIPS) of the United States federal government regarding strong encryption.
Related Downloads
PDF Image

Cloud Connector Instruction

Download this step-by-step instruction to connect and sync your corporate or cloud data sources bi-directional to any native SharePoint 2010/2013/2016 list or library, including Microsoft Office 365.
PDF Image

Feasibility Study: Connecting local SQL Server to SharePoint Online

The study compares different approaches for SharePoint Online data integration regarding performance, features, security and investment.
PDF Image

Flyer: Layer2 Partner Program

Partner with us as a software distributer, reseller or SharePoint, Office 365 or Azure service provider. We are offering a free Partner Program with up to 30% Discount, free NFR licenses etc. Please download for more.
PDF Image

Layer2 Case Study: Cloud Connector helps ChemWerth to integrate the Microsoft Office 365 Cloud

"The Cloud Connector’s interface is easy to understand and use. Anyone familiar with utilities in the Microsoft stack will be productive immediately. Our recommendation: The Layer2 Cloud Connector is a no brainer.”
NoDoc Image

Layer2 Cloud Connector PAD File

This XML-based PAD file offers a Layer2 Cloud Connector product description for resellers to download and publish.
PDF Image

Office 365: How to sync with MS Dynamics CRM

You can sync any CRM data with native lists in SharePoint on-prem or online to make it available offline with Outlook.
PDF Image

Office 365: How to sync with SAP?

You can connect SharePoint and Office 365 with SAP via NetWeaver and OData.
PDF Image

Office 365: How to sync your local file share?

When you are moving to the cloud, there are a lot of reasons to keep your local file system. But you don't have to miss the benefits of the cloud.
PDF Image

Product Flyer: Cloud Connector for Microsoft SharePoint & Office 365

Flyer with features, benefits & pricing info for download as PDF.
PDF Image

Product Flyer: SharePoint Online Local File Server Sync

You can centrally migrate, synchronize, or backup your local file server department shares and home drives very flexible with SharePoint Online to close known gaps and overcome limitations of the Microsoft OneDrive for Business client.
PDF Image

Solutions Flyer: Data Integration with SharePoint, Office 365 and 100+

FileMaker can be integrated with 100+ other sources codeless using the Layer2 Cloud Connector.
Related Links

Layer2 Cloud Connector Community Group @ LinkedIn

​Are you already @ LinkedIn? Please join the Cloud Connector Community Group to share your experience.

Microsoft Partner Story Book for Office 365 about Cloud Connector

​Know more about a Layer2 Cloud Connector customer reference at the Microsoft Office web site.

Purchase Layer2 Cloud Connector via ComponentSource

​ComponentSource has offices in the USA, UK and Japan supporting over 125,000+ Customers, in over 180 Countries. They are also an approved Federal Government supplier through the GSA Schedule.

 

cs-award-top-100-publisher-2014-15-large.gif

Sign Up for Layer2 Product Newsletter

We keep you up-to-date about new Layer2 product releases, added features and fixed issues.