Drupal and Dynamics CRM 2011 Integration
On one of our current projects the client had a requirement that we get data from their Microsoft CRM 2011 system and then sync this data with a new Drupal 7 site. So we had to basically do a data migration and then some custom development to sync the two systems. As always Microsoft provide loads of documentation and there are loads of examples of how to get the data from the CRM. We broke the integration into the following parts so as to make the process a little more manageable.
1. Get details of CRM objects to be stored in Drupal.
This was the easiest part of the process and was basically part of the IA. With a few consultation sessions with the client we realised the objects that needed to be modeled in Drupal, the required fields and the frequency/importance by which they needed to be synced. As we where working on a fixed budget the syncing of objects was an area where we had to set some realistic targets and only a few of the objects would be synced in realtime, the others in the meantime would be updated using cron jobs.
Another reason for the syncing being confined to only a few objects was to do with the fact that not all CRM objects would be editable in Drupal, the Drupal content types would have Drupal only fields. As you can already figure out we were not developing a drupal/crm portal (at the moment) but a very selective view of the CRM data which would be tightly controlled.
2. Write oData queries to get the data.
Now to get the data from CRM, and there are a few ways to go. As usual Microsoft have provided a number of Different Endpoints to use SOAP(using their WSDL) or REST (using their CSDL), these can be found in the Settings->Customizations->Developer Resources section of the CRM.
We decided to use the REST Endpoint as we have quite a bit of experience integrating with third party REST services and also a nice custom framework that we can make use of and save a bit of time. I’d highly recommend limiting the fields per object otherwise the json per object can be huge.
To query the oData Framework you basically have to build up a URL that informs CRM what ObjectSet you wish to retrieve and then the fields, filters etc that you would like returned. I’d highly recommend limiting the fields per object otherwise the json per object can be huge. The syntax is fairly straightforward and slightly resembles a SQL query and as well as applying filters and selecting fields you can select related objects using Expand and limit the number of results etc returned. A very useful tool to use is the Dynamics XRM Tools http://dynamicsxrmtools.codeplex.com/, it will allow you to create the exact url required and also show you how to use the all the different features for querying using oData Endpoint. The one thing I’d mention to watch out for is that the way that CRM 2011 is installed by default limits the number of records returned to 50. If there are more than 50 records then a link to the next page of results will be supplied. This can changed in CRM but unless you really need to I’d leave it alone.
For those of you interested though here is a link detailing how to go about it http://blogs.msdn.com/b/crminthefield/archive/2012/03/16/how-to-increase-the-50-record-page-limit-on-odata-retrieve-responses-for-dynamics-crm-2011.aspx.
3. Create the Drupal Content Types.
As with any Drupal site the content types are one of the most important aspects and with regards to CRM integration a little more thought needs to be applied. A CRM object can be made up of properties some of which are themselves other objects (CRM uses relationships when building objects which is analogous to Drupal entity relationships), your basic OO objects.
The question is when designing your Drupal Content Types do you want to have a content type that incorporates the fields of other related objects or do you want to create further Content Types and have an entity relationship to link them. This really depends on the CRM objects I would say that if the object relationship is always one to one then incorporate the fields in your Content Type however if the object can be referenced by more than one other object then go for entity relationship path.
The other consideration is performance, if your bringing back a lot of fields from related objects it can soon become very unwieldy and increases both the time to query on CRM and the size of the returned data. In CRM objects the Unique ID is a GUID and every object that has relationships with other objects provides this as an ID and also as a URL to get that specific object, we used the ID(GUID) and stored these as a field in Drupal.
4. Write Drupal framework to allow the retrieving, updating and deleting of CRM Data
We made use of our current REST framework which itself uses the excellent Unirest, a set of lightweight HTTP libraries available in [multiple languages](http://unirest.io). Our framework uses controller and model classes and an exception and configuration class. The main change we had to make to how we normally use the framework is around building the query as it isn’t in the format you would normally use with REST, all endpoints are of the format:
http://<your crm server address>/<Your Organisation>/XRMServices/2011/OrganizationData.svc/<ObjectSet?>
We had to handle the different parameters you could use i.e. filters, fields, expands and then also handle the ability to return all results as opposed to limiting it to 50. The one unique thing about this project is that we would never be deleting anything on the CRM side from Drupal but we would be deleting Drupal content from the CRM side. The client wanted the CRM to always be the definitive store and so decided very early on that they would only delete from the CRM.
5. Create the JSON Feeds importers.
Whenever we do migrations we nearly always use the excellent Feeds module https://www.drupal.org/project/feeds and Feeds Extensible Parser (https://www.drupal.org/project/feeds_ex) and always use the JSON parser, it just makes our life so easy. I will quite often reuse a custom drupal module if we’re doing a Drupal to Drupal Migration which creates a json file in the format we want to use plus we can add extra business or formating rules on the data prior to import.
Other times when we’re migrating from say an asp.net web application I’ll reuse a .net client tool we’ve created which also allows us to create the json files for importing and on which we can add business and formatting rules.
For this project the importers where going to be used for two purposes, initially to import all the data from CRM and then for some of the Content Types run on a scheduled basis to only get those objects that have been added, or updated within the last 24 hours. To run the importers on a scheduled basis we made use of the feeds_before_import hook
This allowed us to fetch the json using our REST framework class and then import.
One of the great things about the Feeds Importer is the ability to create entity relationships when running the import, for us it meant we could model the CRM relationships when required using the Unique GUID from CRM as the match. It just works beautifully and the only caveat is that the importers must be run in order so that the related entities exist before they are referenced.
6. CRM Custom Plug-ins
In order for us to be able to push changes from CRM back to Drupal we decided to make use of the ability to create custom plug-ins in CRM, this site was invaluable http://crmbook.powerobjects.com and provides pretty comprehensive documentation. You’ll need to:
- download the developer toolkit from Microsoft https://msdn.microsoft.com/en-us/library/hh372957(v=crm.5).aspx and
- download a version of visual studio.
For our integration we decided to post to our Drupal endpoints only on CREATE,UPDATE and DELETE messages and only for certain objects. When you have your plug in developed then you need to register it using the Plugin Registration Tool that comes with the developer toolkit and at this point you have the decision on whether to have the plugin in isolation mode(sandbox).
As we had to be able to post data to an external site the Isolation mode needed to be set to ‘None’ and to allow this in the Deployment Manager Tool the user account that registers plug-in must have:
- System Admin security privilege ,and
- Deployment Administrator rights.
Once you have your plug-in registered you need to create a step for each action, which can be limited to a specific object and decide when you want the code to execute either Pre-operation (before the object is saved in CRM) or Post-operation(after the object is stored in CRM).
We choose Post-operation as we do not want to have the situation where Drupal cannot be contacted and it prevents a user from modifying CRM data. The other main consideration you have is whether or not you want the plug in to be executed immediately (Synchronous) or queued for later execution (Asynchronous). We decided to use synchronous as we wanted the end user to know if for some reason the update could not be applied to the Drupal site.
This raised an important problem with CRM 2011 namely that when an exception occurs in a plugin you cannot post a message or warning on the CRM page, an invalidPluginException must be thrown which causes an alert box to be showing on the CRM web page. Worse than this, although the data has been saved (if , as we did, you use post-operation) the user will not see the changes applied and unless notified will think that they need to resubmit their data.
For us the way to manage this albeit not ideally, was to inform the user when the alert was showing that their data had been saved successfully and the error was communicating with the external site. This is handled better in CRM 2013 as there is a notification area on a page that a warning/error/message can be posted to. Finally, in CRM 2011 the ability to detect where the request originated within a plugin was deprecated, this is a very important point as you may find yourself in an eternal loop between Drupal and the CRM constantly updating each other. Thankfully there is a way around it in the Microsoft.Xrm.Sdk.IPluginExecutionContext you can use
Based on the the returned value(we’re looking for “WebServiceApiOrigin” ) decide what action you would like to take.
The solution is provided here https://community.dynamics.com/crm/b/hardworkdays/archive/2012/06/01/ms-crm-2011-callerorigin-in-plugins.
7. Create Simple rest Endpoint in Drupal for the actions required only post
The final piece of the work was to be have a rest service that the CRM could use to post changes. Drupal has a few modules that can be used for this https://www.drupal.org/project/restws or https://www.drupal.org/project/services however we wanted something very lightweight and created our own that works with the CRM json we return from the plug-in.
We simply created a few menu paths for the CRUD Operations and then parsed the incoming data and performed the required action on the data, the header contained the security details which we require to be passed on every request.
So there you have it, an approach to integrating CRM with Drupal and although I haven’t covered in any great depth exactly what we did I hope I’ve given you a heads up if its ever something that you find you need to provide for a client.
Finally after having written the custom modules and code for this client it struck me that I should create a more generic approach that given a CSDL endpoint would discover all of the available ObjectSets and from these discover all available properties and relationships. Then a drupal user could decide which objects to create as Entities and automatically create the feeds and import the data for them. This would be a relatively straightforward task although a little time consuming and is something I’m hoping to develop in the next few months.