Best practices are usually created through trial and error. Digia's integration architect Viktor Candolin shares his learnings from working with Microsoft Dynamics CE and its OData API.
This is a loose collection of fairly in-depth technical details I have come across while creating integrations against Dynamics 365 CE, which I think would have been nice to know when I first started out. While there are lots of hits related to these kinds of topics while searching the web, this should be a good starting point from a system integrators perspective. Knowing these details beforehand may affect your architecture or technical decisions when working in an integration role against the Customer Engagement flavour of Dynamics.
This is the first thing you need to get straight, and it is non-trivial. What flavour of Dynamics are you working with? They are completely different products technically, with different API’s and approaches. This article only refers to D365 CE, with its OData API. The idea of them being the same product family is only true for the marketing materials. They also have some capability overlap which is not handled here.
A central point of this blog post is that you can figure out how D365 CE works and what its internal database structure is like purely by using API calls. It is also (mostly) consistent in that all entities work the same way and are visible through the OData API. It is even easier if you have rights to the actual D365 CE user interface.
A good quick reference sheet for the OData protocol can be found here.
The normal CRUD operations you would expect are available, but in addition the OData API standard and D365 CE’s implementation of it allow you to:
The internal data structure in D365 CE is somewhat more complex compared to most programming languages built in datatypes. Notable types are:
When you query data from the CE, by default you only get the raw database ID’s for fields that are optionsets or links to other entities. In many cases what you want is not the internal key of the field value, but the display value of the field. This can be achieved by adding this parameter as a header to the request:
Prefer = odata.include-annotations="*"
Before using this header value, you will get these kinds of property value replies when querying for instance a sales order:
"_ownerid_value": "9fc426cd-538b-e916-a81a-010d3f3a6da8",
After adding the header, it will look like this:
"_ownerid_value@OData.Community.Display.V1.FormattedValue": "Dummy user",
"_ownerid_value@Microsoft.Dynamics.CRM.associatednavigationproperty": "ownerid",
"_ownerid_value@Microsoft.Dynamics.CRM.lookuplogicalname": "systemuser",
"_ownerid_value": "9fc426cd-538b-e916-a81a-010d3f3a6da8",
Even with the above header in place, you will only get the current value of any optionset property. To query all possible values for an optionset property you need to do a separate query per property, which looks like this:
https://your_crm_organisation_url/api/data/v9.1/EntityDefinitions(LogicalName='your_entity')/Attributes(LogicalName='your_optionset')/Microsoft.Dynamics.CRM.PicklistAttributeMetadata?$select=LogicalName&$expand=OptionSet($select=Options)
If your optionset is globally defined, not a custom property of a single entity, last part of the query (OptionSet) needs to look like this:
GlobalOptionSet($select=Options)
For the most part, the D365 CE OData API is pleasant to work with, but in this case not so much. The red fields are values you would need to replace with our own values. The response for most optionsets is too large to display here, and keep in mind that responses may contain localized cleartext versions, not just English values. The need to transfer all optionset values in an integration should be a red flag with regards to work estimates.
A sample response and more in-depth information can be found here.
FetchXML is a more capable querying language for the CRM. It does not have the same limitations as the OData querying options. The relevant differences are:
This is basically batching multiple queries in one request and asking the system to process them all. There are at least three reasons why you might want to use this feature:
The batching is done at the HTTP protocol level, meaning you put multiple HTTP messages in the body of one HTTP message, with a given separator string between each message. Further details here>>
See also the “FetchXML over Batch Query” topic later in this article.
This is a collection of plugins that makes life easier for you when integrating against D365 CE or CDS, in particular this component:
This allows you to build FetchXML queries using a point and click interface, against the actual D365 CE instance you are working with, so custom entities are listed and can be easily used.
This tool allows you to set up connections to Azure Webhooks or Service Bus. It is also important because it shows you the GUID ID of these hooks, which can be used to query failed webhook calls. I will get back to this later.
If you have access to a CE instance as a log-in user, this plugin is worth its weight in gold. Depending on case and customer you may only be given permissions that have API access rights but not UI user rights, in which case this will not be helpful.
Below is a screengrab of the plugin’s UI:
My experience has been that ServiceBus is to be preferred over webhooks. The primary reason is failure management. When anything is wrong on the Azure platform side that would render the Logic App or possible proxy unavailable, you will not get any kind of notification that this has occurred. The CRM will record failed asynchronous calls internally, but typically monitoring these is not something that is considered when building an integration or its monitoring architecture. This means that there are forms of downtime for the Azure platform that leaves no trace in typical integration monitoring that they have happened. Events from the CRM can be easily missed using WebHooks.
To fully cover your bases using webhooks you would need to:
A service bus is more robust than a webhook, and has proper handover mechanisms that ensures that an incoming message will only disappear from the queue when it is truly being processed by a Logic app. There should be no “black holes” in the chain when using a Service Bus with D365. It has a built-in dead-letter functionality. Of course, you need to use these features and set them up correctly for it to be any better than webhooks.
With regards to the previous topic, this topic shows you how to query the set of failed async calls for a webhook. To do this you will need to find out the ID of the event binding in D365 CE that fires the asynchronous call. This binding is typically in Digia done by the D365 CE team and controlling event bindings should not be done by the integration developer. The reasons for this are many, but primarily related to who is responsible for what, and documentation. The same tool that can be used to investigate the binding can be used to change them, but in most cases you as the integration developer should not do so. You can also cause never ending loops by binding ill-considered event triggers to integrations that write something back to the entity they trigger from. The tool is available from the D365 CE SDK, the Plugin Registration tool.
Once you have the tool installed, and connected to the relevant D365 CE instance, you can find the relevant service bus or webhook registration in the list. It should look something like this:
In this case this shows a Service Bus binding where it is fired when update and create events occur related to the msdynactual entity. The exact conditions that trigger it are seen by double clicking the event binding. What we are after is its StepId under properties:
Now that we know the StepId for this asynchronous call, we can query the D365 CE API for failed calls related to this step. Below is a sample FetchXML query that gets the latest 500 possible failed runs related to a given StepId.
<fetch top="500"> <entity name="asyncoperation"> <attribute name="errorcode" /> <attribute name="completedon" /> <attribute name="statecode" /> <attribute name="statuscode" /> <filter type="and"> <condition attribute="owningextensionid" operator="eq" value="7b0dfe42-1111-1111-1111-11113ad7d1ba" /> <condition attribute="statuscode" operator="eq" value="31" /> </filter> <order attribute="completedon" /> <link-entity name="quote" from="quoteid" to="regardingobjectid" link-type="outer"> <attribute name="dg_quotenumber" /> </link-entity> </entity></fetch>
In the code above we also link the regardingobjectid to any possible quote entity and pick its quotenumber should the property be named dg_quotenumber. This code is not usable in your case as is but shows the concept of getting the actual business object related to a failed event, which is referred to by regardingobjectid.
The practice of having a property in relevant D365 CE entities that contains a timestamp of when the entity has last been processed by a specific integration is a good one. I suggest using that architecture for all D365 CE integrations, and work with the D365 CE team and customer to consider if the field should be visible to users. It can bring transparency to the otherwise hidden system integrations activities. But care needs to be taken with the event triggers since if your event triggers for any change to an entity, your own timestamp update will trigger the integration again, creating a never-ending loop. This is best avoided by explicitly setting each and every field in the entity which should trigger the integration event, and not just blanket trigger on any property change.
If you get into using FetchXML in your integrations, you will sooner or later run into a somewhat unexpected problem. The maximum length of an URL when querying the D365 CE OData API. The maximum length is 2048 characters, which you will likely run into far sooner than you will run into the maximum of 10 link-entity statements per FetchXML query.
The solution to this problem is to use Batch Queries, an API feature to perform a batch of multiple queries or one big one. The relevant feature for our purposes is that the query will be put in the body of the batch query message, and so will not run into the 2048-character hard limit. It works like this:
You will need to template your request in some way in any case, because you are sending raw HTML which should then contain your URL-escaped FetchXML query. The way I found convenient was to create a template document file that can be loaded from disk or made a project resource. That template looks like this: (batch_body_template.html)
--batch_xmlfetchContent-Type: application/httpContent-Transfer-Encoding: binary
GET {0}/api/data/v9.1/{1}?fetchXml={2} HTTP/1.1Content-Type: application/jsonOData-Version: 4.0OData-MaxVersion: 4.0Prefer: odata.include-annotations="*"
--batch_xmlfetch--
Into this batch query body template you can then perform a string replace, or do a format string call to insert:
0. The D365 CE instance address
1. The plural entity name your query is targeting (quotes, salesorders, or some other entity)
2. Your actual URL-escaped query
The red coloured fields in the template file shows the corresponding elements that are to be replaced.
You could then take this templating a bit further, and template your FetchXML queries. So that the keyword you are looking for can easily be swapped in:
<?xml version="1.0"?><fetch distinct="false" version="1.0" mapping="logical" top="1"><entity name="account"> <filter type="or"> <condition attribute="accountnumber" operator="eq" value="{0}"/> </filter> <all-attributes/></entity></fetch>
You can then store these queries as files or resources of your integration project. If this sounds sensible to you, here is a ready C# function that loads the batch query template and your FetchXML template from an embedded resource and performs the query against your given D365 CE instance using the RESTSharp library.
A quick note about D365 CE. If you have access to a dev or test instance, you can find out a great deal of information you will need for creating an integration, without being dependent on other people. Follow this path:
1.
2. In the new window that opens, select Settings and Customizations:
3. Then pick this, and don’t worry, you are not yet at risk of changing anything:
4. You will get yet another new window, which is what we are after:
5. When you click the Entities list open, you have all entities defined in the system. Under Fields are the fields of the property, their data types and so on. Under relationships you can find to which entities this entity is linked or links to. You can also find out the UI name of fields that may be seen to you only by their database name, which is useful for communicating with customers.
In summary I think that D365 CE with its OData API is one of the more capable integration subjects I have worked with. It is one of the more open and easily integrated products in the modern enterprise cloud application landscapes. The OData API standard is also an open standard and could be implemented in your possible future API and microservice solutions.