Integrating Dynamics CRM Data into SharePoint 2010

Years ago I worked on a development team looking at the requirements of a good CRM System that could be developed to fit various business models, and one of the key requirements we determined was the ability to pull data from other sources around a business into the solution as a way of integrating business data (possibly as external entities) into the core business CRM Solution.

Sounds simple in theory but can be pretty tricky in practise.

When SharePoint 2007 came on the scene a few years ago, the Business Data Catalogue looked an ideal solution to this requirement – able to define and pull certain areas of data from external Datasources by defining them as Business Data Entities.  However despite the BDC being sold as a ‘no-code’ tool for integrating this data into SharePoint, it did involve specifying your Data Areas via a bespoke XML file – and to me documenting the schema of your data via XML felt pretty much like writing code, and not easy to implement. (honourable mention to Lightning Tools which made this process easier with a workable front-end)

With SharePoint 2010 I thought it was about time to take another look at this topic as the Business Data Catalogue has regenerated into the Business Connectivity Service and now comes alongside SharePoint Designer to allow for easier actual ‘no-code’ solutions to this age-old problem of integrating external sources of data into a single Line of Business Solution.

To look at how this can be implemented in SharePoint and coming from a Dynamics CRM angle, this post will be focused on how we can integrate CRM 2011 data into SharePoint 2010.

To make this post more readable given the technical complexity of SharePoint, the article is broken down into the following sections:

1. SharePoint Designer
2. Defining an External Content Type
3. Using the External Data within other SharePoint Lists
4. Adding Custom Actions
5. Conclusion and References

1. SharePoint Designer

SharePoint Designer is a fantastic little desktop application which connects to a SharePoint site to increase the speed and ease in which standard customisations can be added to the site.

Microsoft first tried this approach by adding functionality to FrontPage (TODO: Link) to connect to SharePoint 2003 sites with mixed results, and I often found it easier to simply perform the customisations using SharePoint’s native Web Interface – however in my experience SharePoint Designer is leaps and bounds ahead of these early efforts and makes adding and customising Custom Lists and other aspects of SharePoint sites considerably easier and quicker than the native Web Interface.

image

The SharePoint Designer home-page

This post will refer heavily to the SharePoint designer (alongside the Web Interface in places) for making the necessary changes to a SharePoint Site to facilitate the integration with CRM Data.

The SharePoint Designer is freely available can be downloaded via the following links:

Download SharePoint Designer (64-bit) http://www.microsoft.com/download/en/details.aspx?id=24309

Download SharePoint Designer (32-bit)http://www.microsoft.com/download/en/details.aspx?id=16573

2. Defining an External Content Type

First of all we need to activate External Content Types within our deployment of SharePoint via the Central Administration area by ensuring that Business Data Connectivity Service is running.

This will allow us to view and amend the list of External Content Types within SharePoint Designer:

image

Showing the list of External Content Types in a SharePoint Deployment via the SharePoint Designer

From here we can add a new External Content Type that acts as a wrapper to show CRM Data within SharePoint.

This is done via the following steps:

(1) Add a new External Content Type with a unique name.

image

Creating the new External Content Type in SharePoint Designer

(2) Define the External System that the External Content Type is reading data back from – the SharePoint Designer allows a external system to be defined from either a .NET Type, SQL Server or WCF Service.

image

Selectiong the type of connection for the External Content Type

For a simple example of how we can connect SharePoint to CRM we can opt for a SQL Server Datasource that then uses the CRM View to facilitate a read-only connection from CRM to SharePoint using CRM’s Filtered Views.  This provides a direct connection between CRM and SharePoint that maintains the CRM Security Model – however this does need to be Connected as the User’s identity such that the Windows Authentication for the account accessing the SharePoint List is used for accessing the Filtered View.

image

Defining the SQL Database details for the External Content Type's connection

With the underlying connection in place, we can choose which table or view to use as the Data Source – in this case, the view for ‘FilteredContact’.

image

Selecting the SQL View or Table to use as the source data for the External Content Type

(3) Define the Read Item and Read List Operations

With the source data selected, different operations can be defined to be made accessible within SharePoint to effect the data.  In this case using the Filtered View for the Contact entity, we can define simple read-only operations for reading back lists of Contacts or an individual Contact Record into SharePoint.  Initially for reading back a single record from CRM:

image

Defining the Read Item Operation for the External Content Type

Defining this operation within SharePoint allows us to define which fields should be read back from the CRM Datasource when viewing a record of this external type.

Initially to define the unique identifier field to be used:

image

Defining the Unique Identifier for the Read Item Operation

And then to define which other fields should be pulled from the CRM Datasource into the SharePoint External Content Type:

image

Specifying which fields from the source data will be used as part of the External Content Type

With the same being done for the List Operation to ensure that the SharePoint External Content Type can be used for showing either individual records or lists of records within SharePoint.

(4) Set Permissions for the External Content Type

With the two basic read operations defined, our last step to configuring the External Content Type is to define which SharePoint users will have permissions to view data from the External Content Type, to do this we must access the External Content Type page from within the SharePoint Web Application as opposed to the SharePoint Designer.

This is done by opening the SharePoint Central Administration area and then browsing Application Management, Manage Service Applications and then Business Connectivity Services.  This then opens the Web Interface for adding or making changes to External Content Types in the same fashion as we have been doing through the SharePoint Designer.

Selecting the External Content Type we wish to modify will then bring up a modification form for the External Content Type – which includes a ribbon button for ‘Set Object Permissions’.  This brings up a prompt to select which Users or Groups should be able to access data through the External Content Type.

image

Adding a particular Active Directory Group or User to have access to work with the External Content Type

This will then be shown in the SharePoint Designer underneath the Permissions tab:

image

How the resulting Object Permissions are shown in SharePoint Designer

(5) Create a SharePoint List to present the External Content Type

With the connection and e are able to create a new SharePoint List based from this External Content Type to make this data available in the standard SharePoint List format:

image

Creating a new SharePoint List to present the External Content Type within SharePoint

NOTE: If at this step you receive a ‘No Finders available in the View Group’ error, this is a generic SharePoint Designer error which can have many causes – in regards to using CRM Data as an external content type for SharePoint this is often related to using the calculated view fields such as ‘accountidname’ that SharePoint then struggles to incorporate into the Content Type.

With the List created we should now have a External Content Type linked to a particular SharePoint List with associated user interface:

image

The resulting SharePoint List connected to the External Content Type

Which also now appears in the list of SharePoint Lists in the SharePoint Designer:

image

The resulting SharePoint List shown in the 'List of Lists' within SharePoint Designer as a External List

From here we have a list of data within SharePoint that is synchronised with a particular CRM entity – such that any data recorded within CRM is accessible via SharePoint:

image

The final List shown within SharePoint, listing the Contact records held in Dynamics CRM

This List can then be modified as per any other SharePoint List to modify the View Columns or Sort Order.

3. Using the External Data within other SharePoint Lists

This external content list in SharePoint can then be used within other SharePoint-only lists as a lookup – providing a method for using reference data pulled from CRM to store data solely within SharePoint via a Lookup field type.

image

Adding a new Lookup field based on the External CRM List on a separate SharePoint Expense List

Allowing entries to be recorded in the list against a list of lookup records which is being synchronised from CRM.

image

Selecting a CRM Contact within SharePoint to be used as the value of the Lookup field in this separate Expense list

Allowing the selection of the lookup value from the list of CRM records, either via automatic text resolution or a lookup window:

image

The Lookup Selector Window in SharePoint showing the list of CRM Contacts available for selection

In effect allowing the selection of a particular CRM Contact for each Expense, in effect building an association between the SharePoint Expense record (in this case, an expense) and the CRM Contact record – bridging the two systems relatively seamlessly.

image

The resulting Item Record in the separate Expense List, linked back to the CRM Contact record through the external list.

This then allows us to effectively synchronise data between CRM (or any other SQL, .Net or WCF system for that matter) into SharePoint, and then put this data to use in SharePoint via linked lists or reporting – further allowing SharePoint to be a ‘one-stop-shop’ of data from around an organisation.

4. Custom Actions

In addition to showing CRM Data within a SharePoint List we can add actions within SharePoint that can act to manipulate the data – in a simple case this can be simple as opening the CRM Form for the selected record from the SharePoint List.

To do this we can define a Custom Action that opens a dynamics CRM URL, which we can add into the SharePoint External Content Type by browsing to the Content Type within the Business Connectivity Services

image

Viewing the External Content Type in the SharePoint Web Interface

This will bring up a New Custom Action form where we can define what this particular action will do – in this simple case, providing the URL in CRM that the Action should open and any dynamic properties (such as the Record Id for the record in CRM that should be opened) that should be supplied to the URL.

image

Adding a Custom Action to the External Content Type through the SharePoint Web Interface

SharePoint’s Custom Action format for parameters allows a parameter to be defined from any of the fields in the External Content Type as a Parameter No – this Parameter No can then be used in the URL via supplying the number as {0}, {1} and so on.  So with ‘contactid’ defined as parameter {0} we can use the following URL to dynamically open the correct CRM Form URL:

http://[CRMServer]:5555/CRMReports/Viewer/DrillOpen.aspx?ID={0}&LogicalName=contact

This then adds the action to new instances (such as lists) where the External Content Type is used within SharePoint:

image

The Custom Action shown within the SharePoint List for each item.

Clicking this action will then open the Contact record within MSCRM:

image

Opening the record in CRM 2011 as a result of the action in SharePoint

5. Conclusion and References

This post outlines how to use the Business Data Connectivity Services Application in SharePoint 2010 to incorporate data from Dynamics CRM into a SharePoint List – but why would we want to do this?  The simple answer is to continue the SharePoint principle of encompassing a one-stop-shop of data throughout an organisation – in this fashion CRM Data can be constantly accessible from within a SharePoint site for publication and reporting outside the standard CRM User Group.

When we consider how SharePoint List Data can then be used within other areas of SharePoint, particularly within Search, this gives a powerful outlet for sharing data and incorporating CRM Data into the central business intelligence of an organisation.

This can be a powerful step to ensuring that SharePoint is the joining piece for integrating an organisation’s data into a single place across many possible disparate systems – and providing a single venue for Content Management and Reporting.  In subsequent posts I may aim to expand on this further, in terms of how a Business Solution can incorporate SharePoint as the ‘hub’ for the business’s individual systems whether they be CRM, Accounting/ERP, HR or indeed any other operational systems.

The following post from the MSDN Magazine was the inspiration for my revisiting this area of SharePoint, and is an excellent read on the topic:

http://msdn.microsoft.com/en-us/magazine/ee819133.aspx

Advertisements
This entry was posted in CRM 2011, Integration, MSCRM, SharePoint and tagged , , , , , . Bookmark the permalink.

10 Responses to Integrating Dynamics CRM Data into SharePoint 2010

  1. Usman says:

    How would you integrate Dynamics with Sharepoint in a way that Dynamics fetch data from SharePoint list and show in Dynamics in a separate frame?
    Please advise.

    Thanks

  2. mzavada says:

    Thanks for the article. I would imagine though that this would not work for CRM online instances since MS will not allow users access to the SQL servers they use, correct?

    • Aye, unfortunately correct – using CRM Online you do not have access to the SQL Views of your CRM Data, preventing SharePoint Designer from being able to base an External Content Type from the SQL Views.

      However there is workarounds in terms of basing an External Content Type from CRM’s WCF Service or developing your own Dataservice which reads back the data via CRM Webservices – this would give a method of creating an External Content Type that uses the WCF/Dataservice as it’s base for viewing the data in SharePoint. So it can be done, but is a more difficult development task.

  3. Andy says:

    I’m getting an invalid credentials message when trying to search the list? I can’t work out why. Any quick tips? Thanks

    • Nick says:

      @Andy, Set permissions at the list you created in SP Central Administration by going to Manage Service Applications -> Business Data Connectivity Service

  4. Pingback: View SQL data from CRM into Sharepoint 2010 BCS failing | Q&A System

  5. Pingback: Dynamics CRM & SharePoint 2010 Integration – Useful Links | daveymcglade.net

  6. guyuming says:

    did you really create lookup for external list, as in https://crmconsultancy.wordpress.com/2011/12/15/integrating-dynamics-crm-data-into-sharepoint-2010/#3 ? i think you mean create column of type External data

  7. Pingback: Dynamics CRM & SharePoint 2010 Integration – Useful Links | daveymcglade.net

  8. Pingback: Dynamics CRM & SharePoint Integration Overview - Microsoft Dynamics CRM Community

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s