CRM 2011 Integration with SharePoint: Custom Document Management

The previous post on CRM and SharePoint integration focused on looking at the workings of the default CRM 2011 functionality to see how the underlying structure of SharePoint Site and SharePoint Document Location records could cater for much more than the basic ‘out-of-box’ Document Management.

This article aims to describe how we can leverage this flexibility via custom development to define a more bespoke Document Management solution between SharePoint 2010 and CRM 2011, using different arrangements of Document Libraries, Folders and an initial look at Metadata.

Developing a Plugin to integrate CRM to SharePoint

Towards achieving a custom format of Document Management between CRM and SharePoint, we can look at creating a Plugin which will automatically integrate a new CRM Record with a new SharePoint Document area for a particular entity or entities in CRM.

This is similar to how custom Document Management Integrations were often implemented between CRM 4 and SharePoint 2007, however with the advent of SharePoint 2010 we are able to use the SharePoint Client Object Model to make this development simpler and less code-heavy. The aim here being to focus on the desired business logic of our Document Management and not get bogged down with the coding for the SharePoint development.

However our first difficulty here is incorporating the SharePoint Client Object Model DLL in the CRM Plugin to allow us to communicate remotely with our deployment of SharePoint 2010. CRM Plugins are standalone DLLs which are then registered to the Database which makes referencing external DLLs such as the SharePoint Client Object Model difficult – however this can be done via a variety of methods:

(1) Register the Plugin DLL via Disk, and then store both the Plugin DLL and SharePoint Client Object Model DLL in the Bin folder of the CRM Website.

This is functional solution to the problem but in impossible for Online or IFD Deployments of MSCRM.

(2) Merge the SharePoint Client Object Model DLL into your Plugin DLL to form a composite Plugin.

This is almost a functional solution, however the merge makes the Plugin DLL very large and often too large to correctly register with CRM – and is a bit of a hammer to crack to a nut really.

(3) Place the SharePoint Client Object Model DLL in the GAC for the Plugin DLL to reference.

This is the most practical solution as allows us to register our Plugin to the Database – but we have no access to the GAC for Online and usually limited access to the GAC for IFD Deployments of CRM.

(4) Develop the Plugin to call out to either a Webservice which can then access SharePoint.(either one of the standard SharePoint Webservices or a custom Webservice which can then invoke the SharePoint Client Object Model)

This is a workable solution for CRM Online deployments but does require SharePoint to be publically facing to accept the Webservice calls – the best bet in this scenario would be to have CRM Online communicating to Public Webservice which in turn communicates to SharePoint Online.

For this example, we will look into option (3) of using the GAC, as this is likely the most appropriate option of On-Premise deployments of CRM.

Adding the SharePoint Client Object Model DLL to the GAC

Assuming we have access to our CRM and SharePoint Servers, adding the Client Object Model DLL to the GAC is easily done.

First of all we can find the SharePoint DLL on the following location of our SharePoint Server where can take a copy for use on the CRM Server – C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI.

The location of the SharePoint Client Object Model on a SharePoint 2010 Server

There is then various ways of adding these DLLs to the GAC, but the simplest is simply to use Windows Explorer to add the files to the following location on the CRM Server – C:\Windows\Assembly.

The SharePoint Client DLLs added to the GAC

With these DLLs copied into the GAC, we should be ready to develop a new CRM Plugin which incorporates the Share Point Client Object Model as a reference.

Referencing the SharePoint Client Object Model DLLs in Visual Studio

Developing the Plugin

For the Plugin to create a new Document Library for each new Account, the logic of the Plugin’s execute method will need to carry out the following steps.

(1) Check whether the Account (or other record in CRM) already has a Document Location associated.

(2) If not, then create a new Document Library in SharePoint

(3) Associate the Account in CRM to the newly created Document Library by creating a new SharePoint Document Location record in CRM.

These steps will involve methods that communicate to both CRM and SharePoint, such that the Plugin will break down into three distinct sets of code: methods to retrieve/insert data to CRM, methods to insert data to SharePoint and the core Plugin Execute call that uses these methods to carry out the logic.

If initially we look at how the code could implement this core logic:

#region Plugin Business Logic

string recordName = string.Empty;
Guid recordId = Guid.Empty;

if ( crmAccount.Attributes.Contains(entityIdField) )
{
    recordId = (Guid)crmAccount.Attributes[entityIdField];
}
else
{
    throw new Exception("CrmConsultancy.CRM2011.CustomDocumentManagement.SharePointIntegration :: Could not find a '" + entityIdField + "' attribute for the " + crmAccount.LogicalName + " record");
}

bool createDocumentLibrary = false;

if (crmMethods.DoesCRMRecordHaveSharePointLocation(recordId) == false)
{
    createDocumentLibrary = true;
}

if (createDocumentLibrary == true)
{
    if (crmAccount.Attributes.Contains(entityNameField))
    {
        recordName = (string)crmAccount.Attributes[entityNameField];
    }
    else
    {
        throw new Exception("CrmConsultancy.CRM2011.CustomDocumentManagement.SharePointIntegration :: Could not find a '" + entityNameField + "' attribute for the " + crmAccount.LogicalName + " record");
    }

    SPSite connectedSite = crmMethods.GetDefaultSPSite();
    if (connectedSite != null)
    {
        // create the Document Library in SharePoint
        SharePointMethods sharePointMethods = new SharePointMethods(connectedSite.AbsoluteUrl, "mySharePointUserAccount", "myPassword", "myDomain");

        string documentLibraryName = sharePointMethods.CreateDocumentLibrary(recordName, "Document Library for CRM Record");

        Guid newSharePointLocationId = crmMethods.AddRootSharePointLocation(connectedSite.Id, entityLogicalName, recordId, recordName, documentLibraryName);

    }
    else
    {
        throw new Exception("CrmConsultancy.CRM2011.CustomDocumentManagement.SharePointIntegration :: CRM is not configured for SharePoint Document Management");
    }

}
else
{
    // CRM Record is already connected to a SharePoint Document Location
    // likely no need to create and link to a new location, take no further action
}

#endregion

We can see here the basic Plugin logic for determining the Record Id and Name from the Account via a Post Image, and using these fields to carry out the logic via calls to either CRM Methods or SharePoint Methods.  The highlighted lines in the code above then refer to the points where these calls are made, if we look at these in turn:

DoesCRMRecordHaveSharePointLocation(recordId)

Method to determine whether the CRM Record already has a related Share Point Document Location associating the record to an area in SharePoint. This is essentially simple query in CRM to return a true/false flag depending on whether a record is found.

internal bool DoesCRMRecordHaveSharePointLocation(Guid recordId)
{
    try
    {
        ColumnSet cs = new ColumnSet(new string[] { "sharepointdocumentlocationid", "name" } );

        ConditionExpression regardingCondition = new ConditionExpression("regardingobjectid", ConditionOperator.Equal, recordId);
        ConditionExpression stateCondition = new ConditionExpression("statecode", ConditionOperator.Equal, 1);

        FilterExpression f = new FilterExpression(LogicalOperator.And);
        f.Conditions.Add(regardingCondition);
        f.Conditions.Add(stateCondition);

        QueryExpression q = new QueryExpression("sharepointdocumentlocation");
        q.Criteria = f;
        q.ColumnSet = cs;

        EntityCollection crmLocations = _service.RetrieveMultiple(q);

        if (crmLocations.Entities.Count > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    catch (Exception ex)
    {
        throw new Exception("CrmMethods -> DoesAccountHaveSharePointLocation (" + ex.Message + ")");
    }
}

GetDefaultSPSite()

Method to retrieve the details of the Default SharePoint Site that is connected to CRM via the Document Management area of CRM – this provides the Plugin with the SharePoint URL and ‘root location’ to invoke for calling SharePoint, again however, this is a fairly simple query in CRM to retrieve the relevant record.

internal SPSite GetDefaultSPSite()
{
    try
    {
        ColumnSet cs = new ColumnSet(SPSite.ColumnSet);

        ConditionExpression c = new ConditionExpression("isdefault", ConditionOperator.Equal, true);

        FilterExpression f = new FilterExpression(LogicalOperator.And);
        f.Conditions.Add(c);

        QueryExpression q = new QueryExpression("sharepointsite");
        q.Criteria = f;
        q.ColumnSet = cs;

        EntityCollection crmSites = _service.RetrieveMultiple(q);

        if (crmSites.Entities.Count > 0)
        {
            return new SPSite(crmSites[0]);
        }
        else
        {
            // no SharePoint Sites defined in CRM
            throw new Exception("CRM does not have any default SharePoint Sites");
        }
    }
    catch (Exception ex)
    {
        throw new Exception("CrmMethods -> GetDefaultSPSite (" + ex.Message + ")");
    }
}

new SharePointMethods(connectedSite.AbsoluteUrl, “administrator”, “myPassword”, “MyDomain”)

This constructor methods creates the initial connection to SharePoint using the Absolute Url field from the [GetDefaultSPSite] method alongside valid security credentials. These credentials could be hard-wired in the code, or added to the SharePoint Site entity in CRM as custom fields.

internal SharePointMethods(string spSiteUrl, string spUsername, string spPassword, string spDomain)
{
    try
    {
        _siteUrl = spSiteUrl;
        _clientContext = new ClientContext(_siteUrl);

        _clientContext.Credentials = new System.Net.NetworkCredential
            (spUsername, spPassword, spDomain);
    }
    catch (Exception ex)
    {
        throw new Exception("SharePointMethods.Constructor --> [" + ex.Message + "]");
    }
}

CreateDocumentLibrary(recordName, “Document Library for CRM Record”)

This method is where the Plugin calls out to SharePoint using the Client Object Model to create a new Document Library.

public string CreateDocumentLibrary(string documentLibraryName, string documentLibraryDescription)
{
    try
    {
        Web web = _clientContext.Web;

        _clientContext.Load(web);
        _clientContext.ExecuteQuery();

        ListCreationInformation lci = new ListCreationInformation();
        lci.Title = documentLibraryName;
        lci.Description = documentLibraryDescription;
        lci.TemplateType = 101;

        List newDocumentLibrary = web.Lists.Add(lci);

        newDocumentLibrary.ContentTypesEnabled = true;
        newDocumentLibrary.Update();
        _clientContext.ExecuteQuery();

        return (_siteUrl + "/" + documentLibraryName);
    }
    catch (Exception ex)
    {
        throw new Exception("SharePointMethods.CreateDocumentLibrary('" + documentLibraryName + "') (General Exception: " + ex.Message + ")");
    }
}

AddRootSharePointLocation(connectedSite.Id, entityLogicalName, recordId, recordName, documentLibraryName)

This method adds a new Share Point Document Location record into CRM that associates the regarding object (in this case the Account record) to the new SharePoint Document Library via setting the Relative Url property the name of the new Document Library.

internal Guid AddRootSharePointLocation(
    Guid siteId,
    string regardingEntityType,
    Guid regardingRecordId,
    string regardingRecordName,
    string relativeUrl)
{
    try
    {
        Entity sharepointLocation = new Entity("sharepointdocumentlocation");

        sharepointLocation.Attributes.Add("name", "SharePoint Location for " + regardingRecordName + "");

        EntityReference lookupSharePointSite = new EntityReference(SPSite.EntityName, siteId);
        sharepointLocation.Attributes.Add("parentsiteorlocation", lookupSharePointSite);

        EntityReference lookupRegarding = new EntityReference(regardingEntityType, regardingRecordId);
        sharepointLocation.Attributes.Add("regardingobjectid", lookupRegarding);

        sharepointLocation.Attributes.Add("relativeurl", relativeUrl);

        return _service.Create(sharepointLocation);
    }
    catch (Exception ex)
    {
        throw new Exception("CrmMethods -> AddSharePointLocation (" + ex.Message + ")");
    }
}

This combination of the Plugin Business Logic, CRM Methods and SharePoint Methods then provides a basic Plugin for automatically integrating CRM and SharePoint each time a new Account (or other CRM entity) is created.

The full Visual Studio Solution and code listing can be found here.

Testing the Plugin

With the Plugin registered against CRM, we can then create a new Account in CRM and immediately have a new Document Library created in SharePoint (via the Client Object Model call) and linked into CRM without any further user action:

Creating the new Account in CRM 2011

Therefore upon saving the Account, we can browse to the Documents area to view the Documents and Folders in SharePoint via the newly created Document Location record:

Automatically created Document Location for the new Account

With this Document Location looking at a new Document Library in SharePoint as opposed to the standard folder inside a pre-existing Library:

Document Library within SharePoint 2010

This gives us a method of effectively controlling how we want our documents managed in SharePoint – in that we are no longer fixed into the default behaviour of creating a new Folder for each Account in a single Document Library, instead the custom Plugin has implemented a new system of creating a separate Document Library per Account.

However whilst this control of the Document Management structure is useful, this simply changes the way SharePoint behaves alongside CRM – our next step could be to bring more sophisticated SharePoint functionality into our Plugin as way of leveraging SharePoint’s strong Document Management features alongside CRM.

Namely Content Types.

CRM, SharePoint and Content Types

To provide a brief summary, Content Types are SharePoint’s method for allowing different kinds of Document to be stored with varying Metadata fields based on the Content Type involved.  In a way similar to CRM’s concept of different entities but with a heavier focus on inheritance.

So a Contract Document may have Metadata fields describing the Contract Type, Sent Date, Effective Dates and Compliance Contact; whereas a Proposal may have different fields for Proposal Type, Revision Number and so on – but both would have standard Document fields for Title, Last Modified and Last Modified By.

In SharePoint terms, this would be expressed as two Content Types for Contract and Proposal inheriting from the base Document Content Type.

Normally Document Libraries created in SharePoint have the Content Type functionality deactivated until explicitly activated in the Advanced Settings of the Document Library.

Therefore any Document Libraries created by the CRM 2011 SharePoint integration will not initially have this enabled – however with our Plugin now creating the Document Libraries via code, this can be changed to activate Content Types for each new Document Library created via the Plugin.

newDocumentLibrary.ContentTypesEnabled = true;
newDocumentLibrary.Update();
_clientContext.ExecuteQuery();

This would allow the Plugin to create new Document Libraries that are able to be attached to different Content Types. If we then create some Content Types in SharePoint, we can then extend this code to attach the Content Types we want to be accessible in this Document Library:

With this done we can alter the code that creates the Document Library to automatically make use of these Content Types for uploading documents:

newDocumentLibrary.ContentTypes.AddExistingContentType(someContentType);
newDocumentLibrary.Update();
_clientContext.ExecuteQuery();

To do this for the Contract Type and Proposal Content Types we have added, our code would have to loop through the Content Types defined in the SharePoint Site and, if found, add them to the Document Library in the same fashion:

_clientContext.Load(web.ContentTypes);
_clientContext.ExecuteQuery();

ContentType proposalContentType = null;
ContentType contractContentType = null;
for (int n = 0; n != web.ContentTypes.Count; n++)
{
    if (web.ContentTypes[n].Name == "Proposal")
    {
        proposalContentType = web.ContentTypes[n];
        _clientContext.Load(proposalContentType);
    }
    if (web.ContentTypes[n].Name == "Contract Type")
    {
        contractContentType = web.ContentTypes[n];
        _clientContext.Load(contractContentType);
    }
}

_clientContext.ExecuteQuery();

if (proposalContentType != null)
{
    newDocumentLibrary.ContentTypes.AddExistingContentType(proposalContentType);
}
if (contractContentType != null)
{
    newDocumentLibrary.ContentTypes.AddExistingContentType(contractContentType);
}

newDocumentLibrary.Update();
_clientContext.ExecuteQuery();

This would have the effect of associating each new Document Library with the predefined Content Types – presenting CRM Users with the option of a different set of metadata fields when adding or uploading a document in CRM:

Creating a new document of a different Content Type to SharePoint through CRM

The key here, as with many of SharePoint’s features, being the tight integration with Office to provide the user a seamless transition between CRM and managing the document:

Creating a new Document in Office directly from CRM with the SharePoint Metadata fields

With the normal SharePoint functionality for uploading existing documents as specific Content Types as well:

Entering the Metadata when uploading a document to SharePoint through CRM

Viewing the list of documents with different Content Types in CRM

This gives us a basic custom structure for how different types of documents may be stored using Document Libraries between CRM and SharePoint – essentially a new custom document management configuration for the CRM Solution through a fairly small amount of custom development.

Conclusion

This concept of using CRM Development to control the Document Management between CRM and Sharepoint allows us a great deal of flexability in how documents for a CRM Solution could be structured.

As we have seen here this allows a Solution Architect to consider other methods of goverance when storing documents provide the initial folder-per-entity structure that CRM provides by default. The inclusion of SharePoint Metadata then allows this to be taken one step forward towards SharePoint best practise for managing volumes of documents – essentially providing methods for taking unstructured document data and streamlining this into a structure that fits the business. (or in a more snappy way, working the way you do, and not you working the way the software does)

Moving beyond this, for the next article in this series I will aim to look into how the concept of SharePoint Metadata can be used alongside CRM for SharePoint Views and Templates to further extend a custom document management configuration, and begin to looking at how we could handle the migration of legacy data into CRM and SharePoint.

Source Code

Download from Public-facing Skydrive