One of the best new additions to CRM 2011 is tight integration with SharePoint 2010 for storing documents. In the past when using v3 or v4, CRM Projects have been forced to develop custom code to integrate CRM with SharePoint, but now the standard CRM functionality allows the two products to be integrated and easily configured in the CRM 2011 settings area.
Following the CTP and release of 2011 at the beginning of this year, many great articles have been written and posted on the web to provide step-by-step guides for connecting CRM to SharePoint using this functionality:
However as with many areas of ‘out-of-the-box’ functionality the default integration is limited in certain ways. This article aims to take a deeper look at this integration between CRM 2011 and SharePoint to examine how the integration works ‘under the hood’; with the aim of understanding how CRM 2011 has implemented this integration and how it could be extended further to meet the requirements of more complex Document Management scenarios.
The Standard CRM and SharePoint Document Integration
By default whenever CRM 2011 has been correctly configured to work with SharePoint 2010, an admin user will be able select which CRM Entities should have SharePoint document areas:
Selecting these entities will then have the effect of creating a SharePoint Document Library for each entity:
Within CRM this will also create a reference in the CRM Database’s Share Point Site table to record that this SharePoint Site will be used for document management – essentially storing the details required to link CRM and SharePoint in this record for reference. We can see this record via a simple Advanced Find in CRM on the ‘SharePoint Sites’ entity:
To fully understand how this is connecting the two systems, it can be useful to look at how CRM has populated the fields for this record as the placeholder that CRM has created to act as a pointer to SharePoint:
We can also open this record in a similar fashion to any other CRM record:
With this in place, we can now browse to any valid CRM Record for the entities selected for Document Management and click ‘Documents’ to begin creating or uploading documents against that record.
This prompts CRM to create a new SharePoint Folder in the Document Library for the entity type of the record – typically the Account entity in many business cases.
With this folder being directly accessible from within CRM via the SharePoint List Component:
Behind the scenes here CRM has taken the step of invoking SharePoint to create a new folder for the Account record and adding a reference in the CRM Database to link the Account record to this particular SharePoint Document Location.
Again we can view this reference record in the CRM Database via a simple Advanced Find, this time on the ‘SharePoint Document Locations’ entity in CRM’:
This list of Document Locations shows the references that CRM has created to external SharePoint Locations for storing documents. In this case, a reference to declare that documents for the ‘Account’ entity will be stored at the relative URL for ‘account’ and a second reference to declare that documents for the ‘CRM Consultancy’ Account record will be stored at the relative URL for ‘CRM Consultancy’.
As this 2nd reference has a parent link to the 1st, this makes the relative URL ‘account\CRM Consultancy’ – and in turn, the 1st Document Location is linked back to the earlier SharePoint Site record and as such the URL is determined as:
[Site Absolute URL] –> [Location 1 Relative URL] –> [Location 2 Relative URL]
In this case ‘http://mySharePointServer/mySite/account/CRM Consultancy’.
We can look at this as a simple ERD Diagram to show the relationships between the entities in CRM and SharePoint:
Opening the child Document Location record, we can see these links in a similar fashion to any other Parent/Child Lookup relationship in CRM:
Looking at the fields for the SharePoint Document Location entity, we can see that these are very similar to the SharePoint Site entity – particularly when thinking of the AbsoluteUrl/RelativeUrl fields.
This series of Site/Location records informs CRM where to look in SharePoint when showing the list of Documents for a record, building a URL from the various levels of relative URLs involved back to the root absolute URL. This is best expressed by the following Record Relationship Diagram that shows how this functions for an Account in CRM:
This basic structure of Document Management allows a CRM Users to add new Document Areas for an Account and record Account-based documents directly through CRM.
Folder Structure Entity
The structure above can be extended via the Document Management configuration in CRM to allow for sub-folders from the initial Account folder down to sub-entities such as Contact. This enables documents to be stored in a nested structure to allow CRM Users to store documents against a Contact or other entity which is in turn linked back to the ‘header’ Account.
Therefore the Record Relationship Diagram would be extended to:
In this example, the Contact entity could be any entity in CRM that is related to the root Account entity – so we could have additional folders for Opportunity, Order or Case for example.
This extended structure stems from the ‘Folder Structure Entity’ field of the SharePoint Site record, this field can be set to either None, Account or Contact to specify the top-level entity for managing documents.
This defines more complex nested sets of Document Location records in CRM:
[Site Absolute URL] –> [Location 1 Relative URL for Folder Structure Entity] –> [Location 2 Relative URL for Record] –> [Location 3 Relative URL for Sub-Entity] –> [Location 4 Relative URL for Related Record]
This allows CRM to cater for business models where the top-level Customer may be an Account (for business-to-business) or Contact (for business-to-consumer) in a similar fashion to how CRM allows Opportunities or Orders to be linked to either an Account or Contact.
However by default this is restricted to Accounts and Contacts, we could not have a custom entity or other system entity acting as the Folder Structure Entity – however this does show the flexibilty that Microsoft have built into the concept of Sites and Document Locations.
The crucial understanding here is that this structure of a Document Library per Entity Type with Folder per Entity Record is simply the default business logic provided by CRM 2011 – and is in no way set in stone as the functionality provided by the SharePoint Site and Document Location entities in CRM can easily be extended to provide bespoke business logic for tracking documents.
As with many areas of Dynamics CRM, the application here provides a platform of functionality that can either be used in it’s default configuration or be extended via custom development.
In terms of why we may want to do this, when we look at the standard SharePoint integration here we can see some limitations for Document Management:
- a single Document Library is storing all the CRM Documents, this could result in a single huge Document Library when it may be better to spread out the documents across multiple document libraries.
- the Document Structure is heavily dependent on nested folders, this can often create a confusing layout for storing documents; and long-time SharePoint experts generally shy away from heavy use of folders for creating good Document Management governance. (SharePoint Document Libraries and Horrors)
- Without customising the Document Library in SharePoint, the Document Structure does not make use of SharePoint Views or Metadata for managing documents.
The next post in this series will attempt to show how we can incorporate these concepts into CRM using custom development that uses and builds on these concepts to provide a custom model for managing documents between CRM and SharePoint.