SharePoint and Dynamics CRM, a marriage made in heaven? As if!
You’d think this is a simple topic. SharePoint, with all its extendibility and connectivity, should be the perfect tool for tracking all your customer related content in conjunction with tracking information in Dynamics CRM.
But reality looks rather grim. The SharePoint connector from Microsoft provides very limited logic as to where to store documents and does not scale well at all. If you have more than a mere 5000 contacts you’ll already start feeling the pinch. More on that later.
Also you may have a business portal with Dynamics AX and an enterprise portal with Dynamics GP, both give users the ability to interact with business data using a SharePoint dashboard approach. But what about the customer portal for Dynamics CRM?
Looks like the Dynamics CRM development team did not read the memo, and there is no out of the box solution to this scenario.
So that’s what we at Professional Advantage are building right now, a customer portal in SharePoint for Dynamics CRM. The foundational concept is that no matter what tool you use, SharePoint or Dynamics CRM, you can get at the most critical information needed for the task at hand.
That means not just storing unstructured data (ie documents) in a folder in SharePoint, but auto-tagging those documents with the customer and opportunity they relate to. Tagging them with supporting metadata, like the industry sector and customer region automatically.
Generating a dashboard that will allow staff to easily see all documents relating to an opportunity, grouped by type and security classification while also having an overview of the customer’s status, such as open cases, contact details and latest billing alerts.
A place for support staff to easily get at infrastructure details so they can better support the customer and store reports and supporting material against the client.
A place for consultants to manage customer projects, store deliverables and contractual documents and also track project status and financial information.
A place where sales and marketing can perform searches across industry and region. Where they can access past deals and case studies easily by drilling down on common metadata.
And finally, a place where the customer can engage us, collaborate on projects, check on outstanding cases and get copies of legal documents.
How are we doing it? Without giving away too many secrets, I can tell you that several components come together: a CRM plugin which hooks into the document management module and creates not a folder but a whole site for a new account.
Using some CSS magic and html tweaks, we embed parts of the site directly into the document management views of accounts and opportunities.
A SharePoint site definition which creates several dashboard style pages with standard BCS web parts, custom filter web parts, lots of xsl and jquery and some event handlers.
A series of library event handlers to take care of the auto-tagging of documents placed against a customer/opportunity.
A series of search pages allowing staff to search across multiple customers to find documents for sector, product, area or other custom criteria.
And most importantly, a flexible web service which allows the CRM data to be surfaced in tags, lists and web parts on the customer portal through BCS driven external content types.
Add a claims-based login mechanism using Azure for customer access and strict security and governance guidelines on what content is shared with the customer through designing the information architecture with compartmentalisation in mind and you have your customer portal for Dynamics CRM!
Now for the techies:
If you ever attempt this yourself I thought I’d share a few gotchas:
1. The samples Microsoft provide are rudimentary at best and don’t scale! The default CRM/SharePoint plugin creates a new folder for each entity. Once you have more than 5000 contacts you can say goodbye to WebDAV access to that data. Say goodbye to performance. Say goodbye to simple navigation. More importantly, say goodbye to data management concepts! Sure you can increase the thresholds but they are there for many good reasons, which I won’t go into today.
So? Build your own which also has scalability in mind. We effectively sucked the creation logic out of the plugin into a web service which also takes care of creating new site collections in new databases for every 1000 new customers.
That way we have much more control over storage allocation and can manage customer bases exceeding a million with terabytes of data.
2. The sample CRM proxy that comes with the SDK does not work that well with BCS. You’ll notice that BCS will try to add the “substringof” parameter to the query for BCS filters. That parameter is not supported by the current implementation of the SDK. Not good. So you’ll need to build your own filter mechanism, and the SDK proposes a LINQ approach. Which in turn would be very scary as the default tries to return all records against which you then do a filter in-code. Grrrr.
A much better approach is to build your own implementation of a proxy. Add your own custom functions which in turn execute queries against the CRM organization web service. When building your web service, you can chose either the WSDL or the WCF OData approach for both your web service and the CRM service endpoint you connect to.
For the final endpoint (ie our own CRM proxy service) we chose WSDL, simply because that format is understood by SharePoint Designer (OData services must be registered and modified via a BCS model) and allowed us much faster prototyping when designing the external content types.
3. So you want to pull BCS data onto a page and only display the correct account information without needing to use query strings everywhere? Create your own filter web part which uses the ITransformableFilterValues interface.
That way you can create your own logic as to where the parameter comes from. Maybe from the site property bag? Maybe from a regex you run over the URL? That’s up to you now.
4. Finally, if you ever try to provision web part connections using the itransformablefiltervalues in your site definition or feature, then I recommend staying away from embedding a WebPartConnection into your onet.xml of elements.xml file as some would suggest.
Instead take the cheeky but much simpler approach of embedding the final ASP.Net markup directly in the page you are provisioning. (Extract it from your prototype using SharePoint designer) This works like a charm as long as you specify an ID on your AllUsersWebPart and use that ID in the on-page markup.
And there you have it – You’re CRM working for you with SharePoint doing all the heavy lifting
Remember to check out CRM and SharePoint Online at the respective Microsoft sites…….