Set Up Your Dynamics CRM to SharePoint Connection
Set Up the Layer2 Cloud
In the Layer2 Cloud Connector, you can create a new connection or simply copy
the "Sample - Dynamics CRM(OData) to SharePoint" connection and edit to fit your
environment. We will start with a uni-directional connection to synchronize the
CRM accounts into the SharePoint list created in the section above. Your new
connection will look something like this:
Fig. Layer2 Cloud Connector example
It is best practice to start with
getting a manual connection working first. You can add scheduling and more
options later on once you've confirmed everything is syncing correctly.
Set Up the Microsoft Dynamics CRM Data Entity
The next step is to prepare data entities for connection. Let's start with
the CRM side.
Fig. Example configuration to
connect to Microsoft Dynamics CRM Online (AccountSet).
To connect, please select the Layer2
Data Provider for OData. It will make use of the Open Data Protocol web services
to synchronize the data.
You need a connection string to connect, authenticate and
request the data. For CRM, you can use something like this:
Please note: The authentication schema DynamicsCRMOnline is for Microsoft
User Accounts, not ADFS integrated user accounts. Please use DynamicsCRMADFS
instead for ADFS. For additional supported authentication types for CRM, see the
OData Provider Documentation. If your type of CRM instance is not listed or
for additional assistance, contact [email protected].
Click Verify Connection String to validate that data is
correct and that the connection works. If it works as expected, click on
Preview Data (in the right-hand Action menu) to check the
columns being pulled. You should see something like this:
Fig. Example data preview of the
Microsoft Dynamics CRM request.
The data preview gives you a
first look at the fields and content - very helpful for the next steps to
improve the query and map the fields to SharePoint columns. This preview should
be the same as if you loaded the OData URI into a browser to view the results as
XML (and this can be a great way to see what fields are available in the
collection, what data types they are, as well as quickly modify the select
query, which is explained in more detail below).
If you took the URL used in the connection string and loaded it into a
browser, you would see something like this:
Fig. Example OData data coming from
the Microsoft Dynamics CRM using the URI in a browser (no need to understand
this in detail).
You can retrieve any kind of
information from your Dynamics CRM instance through this protocol, using the
OData query syntax.
To design an OData query, you can simply use your browser of
choice. Most query conventions are supported for CRM, but not all. For specific
details, see the Microsoft Dynamics CRM OData specification. The Dynamics XRM
Tools also offer an easy-to-use OData Query Designer that runs directly on your
To start with, just enter the following url in your browser to display
an xml-representation of your accounts (replace <your_company> with the
correct CRM url):
You can also add a specific query
parameter to only include specific fields. To retrieve, for example, only the
Company Name you can add a $select – parameter to your query:
Fig. Example of retrieving only the
Account name field with an OData query in a browser.
IMPORTANT The field names in Dynamics CRM are case
sensitive! So for example, AccountName, accountName, and accountname are all
different fields. If you are getting errors that you cannot get that specific
field, check to make sure you have the spelling and case correct.
Once you have built your query, you will add this
to the Select Statement field of the Data Entity in connection
settings. It will be formatted like this - this example selects only the
Name field (account name):
Limiting the number of properties to only the properties needed is
necessary to increase the performance of the overall process. Limiting the
number of retrieved items can also be of interest, as deactivated accounts might
not be needed on the SharePoint side. To filter the accounts, you will need to
use the $filter – parameter as follows:
The two final queries in this example are as follows:
Please read more about general OData query options here:
You can optionally disable inserts, updates and deletes in the Layer2 Cloud
Connector just to be sure that nothing is changed in the CRM instance. Also take
care about the uni-directional and bi-directional option, so that the data is
pushed in the correct direction.
Finally you need a primary key (field with unique values)
to sync. We chose the AccountID in this case and entered it in the Data Entity
form in the Connector.
Set Up the Microsoft SharePoint Data Entity
You will configure the SharePoint list data entity in the same way as done
for the CRM instance, by selecting a data provider and provide a connection
Fig. Example connection to a SharePoint Online contact list.
Please use the Layer2
SharePoint CSOM Provider (included with the Cloud Connector) to connect to
any SharePoint on-premise or SharePoint Online/Office 365. The connection string
for the list "Dynamics CRM" can look like this:
CRM/AllItems.aspx; Authentication=Office365; User
Remember that you have to create the destination list manually with all
columns required to host your query data. Read more about connection strings to
connect to Office 365, SharePoint Online, or SharePoint on-premises here.
Please note that you can connect to any other data destination, such as to a SQL
database, as well.
There is no need to enter a primary key, the provider automatically uses
the SharePoint item ID.
As a last step for the setup, you will need to configure the column/field
mapping. It may look something like this:
Fig. Example field mapping for CRM
Accounts and SharePoint contact lists.
Please note the mapping to the
Title field (usually a required field in SharePoint) and to the additional field
AccountID. You have to add more fields to complete the contact data as usual in
SharePoint and Outlook.
Initiate First Synchronization Run
When all settings are verified, please go to connection root and run the
connection manually for the initial synchronization. Please note, that it could
take some time to transfer all elements, depending on configuration and amount
of data. You can take a look at your SharePoint list to check the progress.
Fig. First synchronization from
CRM to SharePoint.