We do loads of integration with Dynamics CRM / Dataverse from Logic Apps and I wanted to share a couple of patterns we use which I find quite helpful.
If you are using Logic Apps to integrate with Dataverse then you will know there are a set of connectors which do a bunch of the common operations. I know there are a few issues with the connectors and limitations with them but they do cover the basic scenarios for the common CRUD operations.
In this article I wanted to talk about a pattern where we use APIM to implement some common lookups which help our team. If you imagine the scenario where we have a bunch of developers on the team who may be developing system integrations which talk to Dataverse and one of the common patterns you will need is to do lookups for your frequently used entities. If you are querying using the Logic App connector then you will use the List connector with the filter property and it will return an array of items which you then need to get the one you want so you will need to either use an expression or a loop to get the one you need.
What tends to happen in practice is you often have a common master data id which most of your interfaces will use. Below is an example of querying out order table.
The first problem here is every Logic App will have to implement the query and deal with the array coming back.
We wanted to implement a common API operation where we could encapsulate the logic of the common lookups inside APIM in a single operation and then we could let the Logic Apps just call APIM with a simple query and the APIM policy would setup the search criteria for calling the CRM Web API which is the same as what the connector does.
You can see below an example of a query of our Account table where we have a common lookup to query the account by the business partner id. Any interfaces which then need to lookup a CRM account by the business partner can just use the common lookup on APIM and it will just return some key properties from the CRM entity such as the name, address, entity id.
In the call to APIM we can also use the Active Directory OAuth authentication so that it will get a token and flow that to APIM and APIM will just allow the token to pass onto CRM.
When we get the result back I can either setup the API definitions in APIM so I can get all of the properties in the Logic App designer or alternatively I can just use the parse json shape and you can see in the below example I just need a couple of properties such as the name and entity id.
If I need to upload some data to Dataverse I can then supply the account entity id in the update or create row if I need to link to the account and I just made my life a little easier to do this.
A diagram of the flow of this will look like below:
In addition to the reusability benefits of encapsulating these common searches we can also improve performance because we will not be going through the Logic App API Connector which has some well known throttling limits and I believe the APIM connector will go direct from the workflow to APIM so it should not be impacted by the connector throughput limits.
APIM
If we now take a look at the API we setup in APIM you can see I just have a simple operation where I pass the business partner id as a template parameter in the url.
APIM Policy
In the policy for APIM we just do a little work in the inbound and outbound part of the policy to setup the request and format the response data before returning it like below.
<policies>
<inbound>
<base />
<!-- Gets the order id from the policy -->
<set-variable name="accountId" value="@(context.Request.MatchedParameters["business_partner_id"])" />
<!-- Rewrite url for downstream call to CRM -->
<rewrite-uri template="/api/data/v9.2/accounts" copy-unmatched-params="false" />
<!-- Inject the filter query to find the business partner -->
<set-query-parameter name="$filter" exists-action="override">
<value>@("accountnumber eq '" + (string)context.Variables["accountId"] + "'")</value>
</set-query-parameter>
<set-header name="Content-Type" exists-action="override">
<value>application/json</value>
</set-header>
<!-- This will expand the option sets -->
<set-header name="Prefer" exists-action="override">
<value>odata.include-annotations="*"</value>
</set-header>
</inbound>
<backend>
<base />
</backend>
<outbound>
<base />
<set-variable name="responseBody" value="@(context.Response.Body.As<string>(preserveContent: true))" />
<choose>
<when condition="@(((IResponse)context.Response).StatusCode == 200)">
<!-- We can simplify the response by a bit of json formatting in this shape -->
<set-variable name="formattedResponse" value="@{
JObject crmResponse = JObject.Parse((string)context.Variables["responseBody"]);
var crmResponseItems = (JArray)crmResponse["value"];
if(crmResponseItems.Count == 0)
{
throw new Exception("There are no records matching this id");
}
if(crmResponseItems.Count > 1)
{
throw new Exception("There is more than 1 record matching this id");
}
var response = new JObject();
foreach (var item in crmResponseItems)
{
var crmAccountEntityId = item["accountid"];
var crmAccountNumber = item["accountnumber"];
var crmAccountName = item["name"];
response.Add(new JProperty("AccountEntityId", crmAccountEntityId));
response.Add(new JProperty("AccountName", crmAccountName));
response.Add(new JProperty("AccountNumber", crmAccountNumber));
}
return response;
}" />
<set-header name="Content-Type" exists-action="override">
<value>application/json</value>
</set-header>
<set-body>@{
return ((JObject)context.Variables["formattedResponse"]).ToString();
}</set-body>
</when>
<otherwise>
<!-- Do nothing as there was probably an error from CRM which we will just return -->
</otherwise>
</choose>
</outbound>
<on-error>
<base />
</on-error>
</policies>We now have a nice easy common lookup to get the account by the business partner id. We also implemented other searches for things like:
- Get contact by email
- Get order by common order number
- Get railcar by railcar number
- Get invoice by common invoice number
- Get user user by email
And a bunch of others.
Hopefully this approach is also useful to others to just make your Logic Apps a bit easier to maintain and quicker to develop.
