Posted on 9. August 2014

SharePoint Integration Reloaded – Part 2

Part 1 of in this series described how SharePoint Server to Server SharePoint integration (new to CRM2013 SP1) works from the client interface perspective. In this post I'd like to share a bit more how this all works from the server side.

Authentication

When this feature was introduced my first question was about authentication. Having created a fair number of solutions that integrated SharePoint with Dynamics CRM on the server side I knew that this is a tricky area. Since this feature is only available for CRM Online to SharePoint Online where they are in the same tenant it makes authentication slightly simpler because there is already an existing trust in place between the two servers which allows Dynamics CRM to authenticate with SharePoint and act as the calling user. The HTTP request that comes from the client is inspected by the integration component and the UPN is used to authenticate with SharePoint as the same user rather than the service account. This is acting on behalf of the user is critical because when documents are created, checked in/out or queried, it must be performed under the account of the user and not the system account. Perhaps even more important, when CRM queries for documents it will only return those that the user has access to as configured in SharePoint.

If this feature is made available for On Prem customers I would expect that a configuration would have to be made available to provide the user's SharePoint username and password to use when performing server side operations.

Query SharePoint Documents

The new SharePoint sub grid that is rendered by CRM actually uses exactly the same query mechanism as any other entity – but rather than the query being sent to the CRM Database, it is handled by the SharePoint query handler. If you fire up Advanced Find, you'll see a new Entity named 'Documents' but if you query against this entity you will get the error:

The error is given by the SharePoint FetchXml conversion to CAML only works if a specific regarding object is provided – this means that you can only return records for a specific folder, rather than all documents in all document locations. When the refresh button is clicked on the client sub-grid there is FetchXml similar to the following executed:

<fetch distinct="false" no-lock="true" mapping="logical" page="1" count="50" returntotalrecordcount="true" >
    <entity name="sharepointdocument" >
        <attribute name="documentid" />
        <attribute name="fullname" />
        <attribute name="relativelocation" />
        <attribute name="sharepointcreatedon" />
        <attribute name="ischeckedout" />
        <attribute name="filetype" />
        <attribute name="fullname" />
        <attribute name="modified" />
        <attribute name="sharepointmodifiedby" />
        <attribute name="relativelocation" />
        <attribute name="documentid" />
        <attribute name="modified" />
        <attribute name="fullname" />
        <attribute name="title" />
        <attribute name="author" />
        <attribute name="sharepointcreatedon" />
        <attribute name="sharepointmodifiedby" />
        <attribute name="sharepointdocumentid" />
        <attribute name="filetype" />
        <attribute name="readurl" />
        <attribute name="editurl" />
        <attribute name="ischeckedout" />
        <attribute name="absoluteurl" />
        <filter type="and" >
            <condition attribute="regardingobjecttypecode" operator="eq" value="1" />
            <condition attribute="regardingobjectid" operator="eq" value="{1EF22CCD-9F19-E411-811D-6C3BE5A87DF0}" />
        </filter>
        <order attribute="relativelocation" descending="false" />
    </entity>
</fetch>

The interesting part here is that we can add filters not only by regarding object but we could also add our own filters for name or document type. Initially I was confused because running this Fetch query in the Xrm Toolbox FetchXml tester gave no results but as it turns out this uses the ExecuteFetchRequest rather than RetrieveMultiple and this new SharePoint integration is only implemented on the latter.

Internal Execute Messages

This new server to server functionality is exposed by a set of internal messages that are not documented in the SDK but by using Fiddler (the tool that give you super powers!), you can see these messages being called from the client when operation such as Check In/Check Out are called. Here is a list of these internal messages:

Message Name

Description

RetrieveMultipleRequest
(sharepointdocument)

Returns a list of document names from SharePoint for a specific document location. This query is converted into SharePoint CAML on the server and supports basic filter criteria and sorting.

NewDocumentRequest

Creates a new document location and matching SharePoint folder and is called when the documents sub grid is first shown with no document locations configured.

 

FileName – Name of the file to create including extension (e.g. NewDocument.docx)

RegardingObjectId – Guid of the record that the document location belongs to

RegardingObjectTypeCode – Object type code of the record the document location belongs to

LocationId – The ID of the document location to add the new document to (in case there are multiple)

CheckInDocumentRequest

CheckOutDocumentRequest

DisregardDocumentCheckoutRequest

This performs the check in/out operation on a specific document in SharePoint.

Entity – The document to check in/out with the 'documentid' property populated with the List Id in SharePoint.

CheckInComments

RetainCheckOut

CreateFolderRequest

Creates a new documents location in CRM and the corresponding SharePoint folder.

FolderName – the name to give to the SharePoint folder

RegardingObjectId – Guid of the record that the document location belongs to

RegardingObjectTypeCode – Object type code of the record the document location belongs to

 

Now before you get excited you can't use these requests on the server because you will get a 'The request <Request Name> cannot be invoked from the Sandbox.' (Yes, I did try!) This is expected since the sandbox does not have access to the HTTP context that contains the information about the calling user and so the authentication with SharePoint cannot take place.

I proved this using a Custom Workflow Activity that tried to call 'CreateFolder' and you see the following error.

These requests can however be called easily from JavaScript which opens up some interesting possibilities (if a little unsupported because these messages are not actually documented the SDK at the moment):

  1. Automatically create a document location using a different naming convention to the standard one via JavaScript onload of a record if there isn't one already.
  2. Provide a custom view of SharePoint documents using fetchxml – this could even be filtered to just show a particular file type by adding a condition similar to <condition attribute="filetype" operator="eq" value="jpeg"/>
  3. Provide custom buttons to create documents in SharePoint.

I hope you've found this interesting - Next time I'll show you how to get around the sandbox limitation to perform server side operation on SharePoint from a CRM Plugin or Workflow Activity.

@ScottDurow

Posted on 12. January 2013

getServerUrl is Deprecated. Use getClientUrl instead.

Although at the time of writing UR12 is not yet released - I was checking though the changes in the latest SDK documentation.

In addition to the Ribbon Workbench being listed (yay!) I noticed the following statement about the getServerUrl function

"Deprecated. Use getClientUrl instead. This method is deprecated as of Microsoft Dynamics CRM 2011 Update Rollup 12 and the Microsoft Dynamics CRM December 2012 Service Update."

 

Out with the old

 

Using getServerUrl on it's own always had the potential to cause 'Access Denied' messages if the browser domain Url was different to the server Url stored in the CRM database. This was to do with cross domain access restrictions in IE. A work around was to use a method similar to Daniel Cai gives in his post on the subject - http://danielcai.blogspot.co.uk/2012/02/get-right-server-url-in-your-crm-client.html

In with the new

The SDK described the new getClientUrl as:

"Returns the base URL that was used to access the application."

The function nolonger blindly return the server Url in the database - but looks at the url that was used to access CRM so that cross domain access issues will be a thing of the past!

Read more in the SDK:
http://msdn.microsoft.com/en-us/library/d7d0b052-abca-4f81-9b86-0b9dc5e62a66#BKMK_getClientUrl

@ScottDurow

 

Posted on 11. November 2010

CRM 2011 Document Management in SharePoint

Integration between Dynamics CRM and SharePoint has been possible since version 3.0 but involved custom extensions that often proved tricky to maintain. Dynamics CRM 2011 now provides SharePoint integration out of the box.

This post provides a summary of this new integration with SharePoint 2010.

Essentially the integration allows you to create a folder for each entity type (where document management is enabled), and sub folders to store documents relating to a specific record. Using SharePoint 2010, Dynamics CRM 2011 will automatically create these folders for your having the same name as a particular record. To utilise this automatic folder creation, a SharePoint site is set as the default location, and then for each record folder a 'Document Location' record is automatically created. These Document Locations can also be created manually on a per record basis to point to a folder by a absolute URL in any SharePoint site, or by using a relative URL relating to an existing Document Location.

To Set up Document Management in Dynamics CRM 2011:

1) Select System->Document Management->Document Management Settings.

2) In the Document Management Settings Dialogue, select the entities that you wish to enable Document Locations on.

3) Select the Url of the SharePoint site that you want to store documents in by default. Users have the option of adding more locations manually on a per record basis, but this site is the default that will be used.  The default site can be changed later if it becomes full or changed if it has moved - if a new default location is created, new document locations will use the new site, but existing document locations will still be linked to the old site.

4) Click Next

5) In the next dialogue, the SharePoint site is validated. If you select 'Based on entity' you can select the sub folder to store documents underneath.
For example, if you create a document location for a contact 'Joe Bloggs', and then create a case for Joe, and then select 'Documents', a document location will be created underneath the Joe Bloggs document folder. So the folder structure will be:

SharePoint Site/contact/Joe Bloggs/incident/Broken Printer

Interestingly, if you don't have a folder for the contact before you create the case and select 'Documents', the contact folder will not be used, and you'll have:


SharePointSite/incident/Broken Printer

6) By Clicking 'Next' you'll see a message box asking you to confirm that you want to create the root document locations for each entity.

7) Clicking Ok, will give you a progress window, where each folder is created in SharePoint. You must have permissions to the given SharePoint site to create folders otherwise the process will fail.

At the core of the integration there are two new entities:

SharePoint Site - Provides a link to a SharePoint site collection or site.

It has the following attributes:

  • Name - The Name of the SharePoint site. This is set automatically to 'Default Site Collection', or 'Default Site'.
  • Description
  • Url Type - Picklist - Absolute/Relative
  • Parent Site - Lookup - If the Url Type is Relative, this references the parent Site or Site collection
  • Relative Url - Text - If the Url Type is Relative, this provides the sub site location.

Document Location - Provides a link to a document library and folder for a given record. E.g. If you click on 'Documents' in the navigation bar, a folder is created with the same name as the record.

It has the following attributes:

  • Name - The Name of the Location. By default this is of the structure 'Documents on <Site Name> #' Where # is an incremental counter. This can be renamed once created.
  • Description
  • Url Type - Picklist - Absolute/Relative
  • Absolute Url - The
  • Parent Site
  • Relative Url - The folder name where documents are stored. It cannot be more than one folder, so if you have nested folders, you must create a parent Document Location, and reference it in a sub Document Location.
  • Regarding - Empty for root document folders (contact, account, incident etc), and for specific record folders, the lookup field references the specific record that the document folder is associated with. Essentially this is the same as the 'Regarding' field on activities.

So if you enabled document management on contacts and incidents, you would end up with the following Document Libraries:

Name

Parent Library /Location

Relative Url

Regarding

Documents on Default Site Collection 1

Default Site Collection

contact

 

Documents on Default Site Collection 2

Default Site Collection

Incident

 

Scenario 1 - A contact with 1 case with the 'Based on entity' unchecked.

Name

Parent Library/Location

Relative Url

Regarding

Documents on Default Site Collection 1

Default Site Collection

contact

 

Documents on Default Site Collection 2

Default Site Collection

Incident

 

Documents on Default Site Collection 3

Documents on Default Site Collection 1

Joe Bloggs

Joe Bloggs

Documents on Default Site Collection 4

Documents on Default Site Collection 2

Broken Printer

Broken Printer

Notice that the parent library/location for the contact and case is set to the root document location that was created by the Document Management Settings wizard.

Scenario 2 - A contact with 1 case with the 'Based on entity' is checked.

Name

Parent Library/Location

Relative Url

Regarding

Documents on Default Site Collection 1

Default Site Collection

contact

 

Documents on Default Site Collection 2

Default Site Collection

Incident

 

Documents on Default Site Collection 3

Documents on Default Site Collection 1

Joe Bloggs

Joe Bloggs

Documents on Default Site Collection 4

Documents on Default Site Collection 3

incident

 

Documents on Default Site Collection 5

Documents on Default Site Collection 4

Broken Printer

Broken Printer

Notice that the broken printer case document folder is created underneath the contact folder:

Contact/Joe Bloggs/incident/Broker Printer
  • Figure 1 - UML representation of CRM 2011 SharePoint concepts

    The actual interface to SharePoint is provided by the CrmGrid page that must be installed on the SharePoint server. The installation notes provide good instructions on how to get it installed: http://go.microsoft.com/fwlink/?LinkID=200050.

    Because the SharePoint integration is an Iframe, the client security context of the current user is used and so you must have access to the SharePoint site to perform operations from within Crm. There is no linkage between CRM and SharePoint Security.

    When you click on 'Documents' inside an entity form, you will be prompted with:

    Clicking Ok, will create the document folder, and document location record. The document library view inside CRM looks like:

  • You can select from multiple locations associated with the same location using the Document Locations drop down.

    If you fill up your default site, you can add a new SharePoint Site through the Document Management Settings->SharePoint Sites list, and select 'Make Default'

     

    Notes:

    1) Since the folder in SharePoint is named using the display name of the record, if you subsequently change the name in CRM (e.g. change the name of the contact, or rename the title of the case) you will receive a 'library not found' error when clicking on the 'Documents' navigation bar item. CRM 2011 then gives you the option to re-associate the record with a new location. This usually would mean renaming the folder in SharePoint and then updating the document location record in CRM.

    2) There is no synchronisation between the security roles in CRM and those in SharePoint. For this reason you must secure your SharePoint site manually. If a user attempts to use the automatic folder creation feature, but they don't have access to the SharePoint document library, the operation will fail.

    3) Some CRM 4.0 integrations to SharePoint allowed you to create a sub-site per entity record. This would still be possible with the CRM 2011 integration with some coding, since you can create your own document locations and relate them to the given record. A Plug-in could be created to automatically create a sub-site and create a corresponding SharePoint Site and Document Location when a record is created. Since the default site folder creation only runs if there are no Document Locations for the current record, you would then simply be given the custom document location rather than a default one created.

    4) Note attachments and email attachments are still stored in the CRM database, and currently there is no easy way of removing the ability for uses to create attachments in CRM without removing notes all together. Email attachment support is tightly coupled to the CRM database storage of documents, and so this is always going to be needed. A custom plugin could be written to 'harvest' note/email attachments and automatically add them to the associated document location.

    5) If you enable Content Types on your document libraries, when you add a document via CRM, since the interface is essentially just SharePoint, you are still promoted to select the Content Type and provide additional metadata.

    6) The automatic folder creation functionality is only available when you are connecting to SharePoint 2010, although Document locations will work with SharePoint 2007.

    Conclusions

    The out of the box support for SharePoint in Dynamics CRM2011 does a good job of providing a simple and low maintenance option for users to store and retrieve documents in SharePoint  from within Dynamics CRM records. Users can then benefit from SharePoint's searching and document management features. More importantly, it is designed in such a way that it can be extended by Developers to support more complex integration scenarios.