Posted on 19. February 2016

SharePoint Integration Reloaded – Part 5 (belated)

In previous articles in this series we've talked about the differences between Server Side Sync and the old List Component. Since I published the first articles, a new MSDN article on the topic has been posted which I thought would be good to signpost folks to => Important considerations for server-based SharePoint integration.

One of the topics that has come up recently for people using Server Side Sync to SharePoint is the 5000 item limit of Document Libraries which has led to a bit of panic amongst some so I thought I'd dispel the rumours!    

Here are the facts about the Throttling Limitation

1) You can see how many items you have in a Document Library by opening the site in SharePoint and selecting 'Site Content' from the left hand navigation menu. The number of items will show below the Document Library name – this includes documents and folders.

2) If you have more than 5000 items you can still use Server Side Integration with SharePoint provided that you only use the default sort order of the document view in CRM.

The default sort is by Location Ascending. You can change this to sort Descending but if you change the sort to any other column you will receive the error "Throttling limit is exceeded by the operation"

3) If you have a record with only 2 documents in the associated document locations folder you will still not be able to sort by any other column other than Location if the root Document Library has more than 5000 items overall.

3) If the user clicks on 'Open SharePoint' then they will be able to do all the sorting they need since the limitation is not experienced by the native SharePoint interface – only the CRM document lists.

I find that this sort limitation is not an issue because I encourage users to use SharePoint freely due to its rich interface. Don't try and hide SharePoint from them because it's important to understand the way in which documents are stored and the additional features that SharePoint has to offer. The documents view in CRM is simply to be used as a quick reference for documents associated with the CRM Record.

Hope this helps!

Posted on 9. April 2015

Control Async Workflow Retries

The Dynamics CRM Async Server is a great mechanism to host integrations to external systems without affecting the responsiveness of the user interface. Once such example is calling SharePoint as I describe in my series – SharePoint Integration Reloaded.

A draw back of this approach (compared to using an integration technology such as BizTalk) is that any exceptions thrown during the integration will simply fail the workflow job with no retries. You might already know that the Async Server does in fact have a retry mechanism built in as described by this excellent post from the archives - http://blogs.msdn.com/b/crm/archive/2009/03/25/when-do-asynchronous-jobs-fail-suspend-or-retry.aspx. As you'll see from this article there are some built in exceptions where CRM will automatically retry a number of times as defined by the 'AsyncMaximumRetries' deployment property. The interval between these retries is defined by the 'AsyncRetryBackoffRate' property.

So how can we make use of this retry mechanism with our custom code?

There is an undocumented and unsupported way of using this retry mechanism in your custom workflow activities. I first used this technique back in the CRM 4.0 days and I'm pleased to report that it still works with CRM 2015!

Although unsupported, the worst that could happen is that the workflow would stop retrying in future version of Dynamics CRM and revert to simply reporting the exception. However it still wouldn't be a good idea to rely on this functionality for mission critical operations.

So…plz show me the codez…

catch (Exception ex)
{
    // TODO: Rollback any non-transactional operations

    OrganizationServiceFault fault = new OrganizationServiceFault();
    fault.ErrorCode = -2147204346; // This will cause the Async Server to retry
    fault.Message = ex.ToString();
    var networkException = new FaultException(fault);
    throw networkException;
}

When an exception is thrown by your custom workflow activity you'll see the workflow enter into a 'Waiting' state and the 'Postpone until' attribute will be set to the time when the retry will take place. Once the maximum number of retries has been performed, it will enter the 'Failed' status.

You can use this technique to ensure that any temporary integration failures such as service connectivity issues will be dealt with by a graceful retry loop. It only remains to ensure that you before throwing the exception you rollback any operations performed already by your code (such as creating records) so that your retries will not create duplicate records.

Hope this helps!

@ScottDurow

Posted on 29. December 2014

SharePoint Integration Reloaded – Part 3

In Part 1 and Part 2 of this series we have discussed how the new server-to-server integration with SharePoint works under the covers. In this post I'll show you how to integrate with SharePoint directly from a sandboxed workflow activty/plugin rather than relying on the out of the box integration.

Using the standard integration, a new folder will be created for each record underneath the default site. In some solutions you'll find that you want to modify this behaviour so that folders are created in a custom location. You may for example want to have an opportunity folder created under a site that is specific to a particular client rather than all under the same site.

The challenge with integrating with SharePoint using a CRM Online Workflow Activity/Plugin is that you can't reference the SharePoint Assemblies which authenticating and calling the SharePoint web service somewhat harder. Thanks goes to fellow Dynamics CRM MVP Rhett for his blog that provided a starting point for this sample - https://bingsoft.wordpress.com/2013/06/19/crm-online-to-sharepoint-online-integration-using-rest-and-adfs/. The sample code in this post shows how to create a folder in SharePoint and then associate it with a Document Location. The authentication with SharePoint works via ADFS and since the out of the box integration uses a trust between CRM and SharePoint that is not accessible from a sandbox (even if you try and ILMerge it!) we have to provide a username and password that will act as our privileged user that can create folders in SharePoint. I have left a function where you can add your own credentials or implement a method to retrieve from a secure entity in CRM that only administrators have access to. Look in the code for the 'GetSecureConfigValue' function.

The sample contains a custom workflow activity that works in a CRM online 2013/2015 sandbox accepting the following parameters:

  • Site – A reference to the site that you want to create a folder in. You could store a look up to a site for each customer and populate this parameter from the related account.
  • Record Dynamic Url – The 'Dynamic Record Url' for the record that you want the SharePoint document location to be related to. This uses my Polymorphic input parameter technique. You simply need to pass the Record Url (Dynamic) for the record that you wish to create the folder for.
  • Document Library Name – The name of the document location to create the folder underneath. In the out of the box integration this is the entity logical name (e.g. account)
  • Record Folder Name – The name of the folder to create. You could use the client name, client ID etc. – but it will automatically have the GUID appended to it to ensure uniqueness just like the out of the box integration.

Calling the workflow activity might look like:

The workflow activity is deployed using the Developer Toolkit for Dynamics CRM and performs the following:

  1. Checks if the document location already exists for the given site/document library – if so it simply returns a reference to that
  2. Checks if a document location exists for the given document library – if not, one is created
  3. Creates a SharePoint folder using the SpService class. It is worth noting that if the folder already exists, no exception is thrown by SharePoint. The SpService class must first authenticate using the SpoAuthUtility class.
  4. Creates a Document Location for the newly created folder.

You could choose to run the workflow in Real Time or asynchronously on create of a record – the down side of real time is that it will increase the time that the record takes to save.

Check out the code in MSDN Samples- you'll need to do a Nuget package restore to pick up the referenced assemblies.

View/Download Code

That's all for now – have a Happy New Year!

@ScottDurow

 

 

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 4. August 2014

SharePoint Integration Reloaded – Part 1

Back in the day when CRM2011 was first in beta I blogged about the exciting SharePoint integration and how it works. This post is about the exciting new server side SharePoint integration that is now available as part of CRM2013 SP1 Online.

There has already been some good posts on how to set up SharePoint sever-side Sync but in this series I'm going to explain how the server to server integration works in more detail and run through some scenarios of how it can be used for custom solutions.

CRM List Component Integration

Before CRM2013 SP1 was released the only option for SharePoint Integration was to use the CRM List Component. Each document location was surfaced on a record form record via an IFRAME that placed a SharePoint page inside the CRM page via the list component aspx page. This SharePoint page rendering the document library's default view with a CRM theme and provided the upload/download commands.

  1. The CRM form page is displayed in the browser and including an IFRAME that requested the configured document library page from SharePoint.
  2. The IFRAME shows the SharePoint document library styled to look like CRM. This required the user to be independently authenticated with SharePoint.
  3. Using any of the actions in the page (New/Upload etc.) sends requests directly to SharePoint.

Changing Landscape

This approach worked well but since the user was accessing SharePoint directly within the IFRAME they'd sometimes encounter authentication issues where they must be authenticated with SharePoint first and sometimes SharePoint needed to be configured to allow inclusion of content in IFRAMES. In addition to this the list component required a sandbox host to run but this feature is being phased out in SharePoint Online.

Server to Server SharePoint Integration (S2S)

With the introduction of CRM2013 SP1 a new type of integration has been developed that provides direct server to server integration between SharePoint and CRM thus removing the need for the user to be pre-authenticated with SharePoint on the client.

  1. The Record page includes a standard sub grid that is populated using the CRM entity query object model. CRM converts a RetrieveMultiple request on the SharePoint Document entity into SharePoint CAML (Collaborative Application Markup Language) query and sends it to the SharePoint Web Services. The important part here is that this query is run in the context of the currently logged on user and so they only see the document that they have access to in SharePoint (more on how this works in part 2 of this series).
  2. Documents are rendered inside the CRM Form HTML as a standard sub grid in the same way that any other record might be displayed.
  3. Using the New/Upload command bar buttons sends a request to CRM by way of an Execute Request in the same way that any other command bar buttons might do.
  4. CRM uses the SharePoint Web Service API to execute the requests and refreshes the sub grid.

This server to server integration only works for CRM Online/SharePoint Online combinations that are in the same tenant due to the nature of the server to server authentication and can be turned on in the Document Management Settings using the 'Enable server-based SharePoint integration'. There is a note that states that sandboxed solutions will not be supported in the future for SharePoint online.

Differences between List Component and Server-to-Server

Once S2S integration is enabled you'll see a similar view to the list component but it looks far more CRM2013 like. Apart from a slicker interface there are a few other differences:

Folder Support

The S2S sub grid doesn't support folders within the document library and so all documents are flattened down underneath the document location folder. The Location column does give you folder name which you can sort by to allow grouping by folder.

Custom Views

The great thing about having the documents queried by CRM is that you can create custom views of documents in the same way you would with any other entity in CRM. When using the list component the default view in SharePoint was rendered in the IFRAME meaning that to get new columns you had to have list customisation privileges on SharePoint such that all users would see the changes. With the new server to server integration you can select SharePoint columns to include in your own views and even add in your own filters using the CRM advance find interface. If you think about it – this is very cool!

Item Actions

The List Component was by nature very similar to the SharePoint list user interface and so it had more or less full support of actions that can be performed from SharePoint (with the exception of workflow operations). The server to server sub-grid provides all the main functions but with some options such as Alert Me, Send Short Cut, View History and Download a Copy being unavailable.

The S2S integration gives the following command bar actions:

This is in comparison to the List Component actions that are as shown below.

Inline Dialogs

With CRM2013's single page user experience any pop-out windows are supposed to be kept to a minimum. When using the list component operations (such as check-in/out) a new window would always pop out but with S2S integration an inline dialog shown instead. This really make it feel tightly integrated and slick.

Out of these differences, the lack of folder support is the only one that has had any significant effect on my solutions but actually can be seen as an advantage if using sub-folders to hold child entity documents. In this scenario all documents will be visible from the parent record's document view rather than rely on the user drilling down into each folder to see content.

That's all for now but in the next article in this series I'll show you more of how this functionality works under the covers.

Read Part 2

@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.