D365 Web API Batch Request, Transaction Integrity, and Logic Apps – Part 2

In my last blog, we looked at achieving transaction integrity using changesets with the batch request. The only problem with the batch request is that you can’t use the record created in initial requests in the subsequent requests. E.g., to create an order, we create an order record first then create all the line items related to the order. In a batch request, you can create the order record but can’t link that record to the order line items.

There are a few options to overcome this issue. This blog is discussing two of those options.For this blog, I am using the logic app created in the last blog (http://mscrmshop.blogspot.com.au/2018/03/d365-web-api-batch-request-transaction.html). This blog creates a contact record with two address records.

Option 1(Create the Contact record GUID Manually)

The one option is to generate a GUID before invoking the batch request and use this GUID  to create the contact record and related address records.

The following screenshots depict the changes in the logic app.

• The first screenshot displays all the steps for the logic app.
• The second screenshot displays the step to generate a new GUID for the contact. This step is using Initialize Variable action.
• The third screenshot shows the part of the batch request. The screenshot also highlights where the GUID is used to create a new contact and related address records.

Note:

The creation of a manual GUID is not recommended for performance reasons. If the number of users and transaction volume can handle the performance hit, only then use this option. Otherwise, consider the second option.

Option 2 (Deep Insert)

The second option is not to use the batch request. The transaction integrity can be achieved using the deep insert.

This option allows us to create a new record and its related records in one operation. If the creation of any of the records fails, the system fails the whole operation.

The following screenshot displays the deep insert web API call.

Update Considerations

The above two options are useful for creating new records. If you want to update multiple child records based on a parent record, then consider making two API calls:

• The first call to retrieve the GUID of the parent record based on available attribute/attributes (e.g., first name, last name, email, and address)
• The second call (batch request)  to update the child records based on the GUID retrieved in the first call.

Download Links

You can download the logic apps created for this blog from the following links:

• Batch Request with GUID
• Deep Insert

That is it. If you have a better solution, please share it.

D365 Web API Batch Request, Transaction Integrity, and Logic Apps – Part 1

In my last 2 blogs, I have written about calling D365 Web APIs from Logic Apps. This blog is the real reason why I was exploring the options of calling D365 Web APIs directly from Logic Apps, instead of using the Dynamics connector provided by Logic Apps.

In real life, the integration is not always about creating individual records. In my experience, a lot of integration scenarios are related to transaction integrity. This means that a group of related Create, Update and Delete requests are executed as one logical transaction. Transactions are useful in scenarios where you have to commit a group of operations/requests in entirety, and ensure that the system will roll back all changes if any of the requests fail.

In general, most of the synchronous plugins, synchronous workflows and actions support transactions, but it is not recommended (best practices) to use them for integration. D365 also provides the ExecuteTransactionRequest method to support transactions. It is a part of the Organization Service (“SOAP Endpoints”). Using this means that you have to add Microsoft.Crm.Sdk.Proxy.dll and Microsoft.Xrm.Sdk.dll assemblies to your projects.

So what would you do if you are using Logic Apps as an integration layer? One solution is to use the Web APIs Batch operation. Please check the details of this command on https://msdn.microsoft.com/en-us/library/mt607719.aspx. The primary purpose of the Batch operation is to bundle multiple requests/operations into one HTTP call.  The Batch operation provides changesets to support transactions.

For this blog post, I have used CRM Web API’s Batch operation to support transaction integrity.

Scenario

I am keeping the scenario very simple. An external system is calling the Logic Apps with contactid, billing address, and shipping address.
The Logic App should create both the addresses linked to the contact with the contactid.  If any of the requests fail, CRM should roll back all the requests. The following screenshot displays the body of the request.


In real life, the change set can contain requests to create/update/delete different kinds of records.

Solution

We have used CRM Web APIs Batch operation to achieve transaction integrity for this scenario. For this blog, I have used the Logic App created in my previous blogs.

The Logic App receives the request, get the authentication token and make the batch request. If the call is successful, the Logic App terminates with the status of Succeeded, otherwise it creates an integration log record in CRM and terminates with the status of Failed.

The updated Logic App has the following steps. Steps 4 and 5 are exactly the same as my previous blog.

Step 1

It is an HTTP trigger. It is receiving the request. The payload schema has been updated based on the body of the request.

Step 2

The Web API Batch operation requires a unique batch number and changeset number. This step initialises the batch number for the request. I have used Batch_guid as the format of the batch number.

Step 3

This step initialises the changeset number for the request. I have used changeset_random number (100-999) as the format for the changeset number.

Step 4 and Step 5

These steps are exactly the same as my previous blog Calling D365 APIs from Logic Apps. These steps get the authentication token and parse the token so it can be used to make the Web API call.

Step 6

This is the most important step. It is using a HTTP action.


The request body is quite big for a batch request. The screen is just displaying the start of the body and the first request to create the customer address. To make the batch request, the following information is required.

• Use Post as Method.
• Provide the D365 Web API Uri. The last part of the Uri is the name $batch .
• Headers are the same for all the Web API calls except content-type. The content-type for the request is multipart/mixed boundary =BatchNo
• The body of the request contains all the individual calls. There are specifics on how to use batch no. and change set no. in the request. Be mindful of that. I have provided the whole app definition at the end of this blog.

Step 7

This step will create an integration log record in CRM in case of an error in Step 6. I have used the Dynamics 365- Create a new record action for this step.



One of the reasons to create an error log is that the output of the batch request is the base64 format. The error messages are not in human-readable form. When we log them, the Logic App automatically decodes the base64 strings.
Note: I am logging the whole output body in this step. In real life, you can use the substring function to log just the error description part of it.

Step 8

The purpose of this step is to terminate the Logic App with the status Failed. Steps 7 and 8 will be executed only if Step 6 fails.

Results

I have used Postman to trigger the Logic App.

Successful Execution

If the call is successful, you will be able to see 2 addresses related to the contactid provided in the request body.

Failed Execution

To test the failure scenario and transaction integrity, I manually updated the addresstypecode to 8 for the second request in the changeset.  This addresstypecode does not exist in the CRM. The Logic App logged the following error in CRM, and there were no address records in the system. The error was in the second request, but the system has rolled back the creation of the first record too. That is how you implement transaction integrity.

Where From Here

This was a simple example, but what if we had to create a Contact record first and then create Addresses linked to the Contact record created as a part of the transaction?
Please share your solution in the comment section. I have a few things in my head, which we will talk about it in the next post.

Logic App Definition

Calling Dynamics 365 actions from Logic Apps

Continuing on from my last post, “Calling D365 Web APIs from Logic Apps”, this post will walk you through how to call D365 actions from Logic Apps.
I have created a global action. This action is not doing anything meaningful yet, it simply takes an input parameter and appends “Your Input was” to the front of it, and returns it as an output parameter. Here are the screenshots of the action.


The above screenshot displays the Assign step of the action.

Calling the action from Logic Apps

I have used the Logic App I created in my last blog and modified it slightly. I have only made changes to Step 4 of the app.

For calling an action:

• Use Post as Method.
• Provide the D365 Web API Uri. The last part of the Uri is the name of the action I am calling. In this case it is new_ActionLogicApp.
• Provide the input parameter of the action in the Body.
  {
    “Input”: “MyString”
  }

Save the Logic App and test it.

Result

I have attached a screenshot of Step 4 after execution.


So there we go, we have successfully called a CRM action from the Logic App.

Calling D365 APIs from Logic Apps

Logic Apps are my favourite topic from last few months. The blog is about calling CRM Web APIs from Logic Apps. Now the question is, why do I want to call the CRM Web APIs directly when Logic Apps provides a Dynamics 365 connector out of the box?

Why call the CRM Web APIs directly?

The main reason is that the Dynamics 365 connector provides only the following actions:

• Create a new record
• Update record
• Delete a record
• Get record
• List records

But CRM Web APIs provide much more functionality than listed above. What if you want to call an action, a function, bulk Create/update, transaction integrity, and much, much more? In these scenarios, you want to call the Web APIs directly, instead of using the Dynamics 365 connector.

Authentication Approach

For this blog, we will be using the single tenant server-to-server (S2S) authentication. The best part of using this approach is silent authentication, instead of redirecting the application to the Microsoft login page. Secondly, we can use the “application user” (integration user) to connect to CRM, which does not need a user license, instead of a real user. We can assign the minimum privilege security role to this account as a best practice.

Registering your app to access D365 and creating an application user

Please follow the linked article to register your Logic App in Azure AD and create an application user in D365.
https://msdn.microsoft.com/en-us/library/mt790170.aspx
The article’s heading is Use Multi-Tenant Server to Server authentication, but the steps are exactly the same for single tenant.

Creating a Logic App

We will be using the clientid (applicationid in Azure AD) and secret-key to get the authentication token from Azure AD, and use that token to make a Web API call.
The Logic App we are creating for this blog is very simple and has 4 steps.

Step1

This is an HTTP trigger that can be called from the external application to trigger the Logic App. This is empty for this exercise, but will be used to pass the information to the Logic App from external data sources in future blogs.

Step 2

This is the main step. I used an HTTP action to call Azure AD for the authentication token.

• URI- It is an Azure AD authentication URL to get the authorisation token. The guid in the URL is the tenant ID that you can get from the Azure AD’s properties.
• Headers- I used Fiddler to capture the request and retrieve the headers.
• Body – In the body I am providing the grant_type, clientid, client_secret and resources values. I got some help from https://alexanderdevelopment.net for this. He was using the grant_type of password and providing the CRM user and password in the body.
• Authentication – it is set to none. I tried to use Oauth 2.0 for authentication and provide the required values, but it never really worked.

Step 3

This step is simply using a JSON parser action to parse the output of Step 2 to use in Step 4.

Step 4

This is the final step that uses an HTTP action to make a CRM Web API call.


Method- I am using a GET method.
URI – The CRM Web API URL. I am using the GET method to retrieve the top 2 Accounts.
Headers – Most of the headers are taken from sample requests from Microsoft websites. The most important of them is Authorization. It is set to the Bearer access_token retrieved in Step 2.

Run the app

Run the app manually. If you get all green ticks then it means everything is working as expected. Double click on the steps to check the input and output of the step.
The following screen shots display the input and output of the HTTP-GetToken and HTTP-Call CRM API steps.

Logic App definition

Replace the clientid and secret-key and test the app.

OCR Dynamics CRM email attachments

I am very excited about this blog. I was watching a Logic App demo in one of the Microsoft Ignite videos where an order record was created in an ERP system from an order image using Optical Character Recognition (OCR). I am trying to do the something similar using CRM email attachments.

Scenario

The scenario is when a new CRM email with the image attachments is received/created, the image attachment will be processed by OCR, and the response will be used to create a new Lead record in CRM.

Solution Overview

The most exciting part of this solution is the number of Azure services used in this solution. The Logic App is used as integration layer for the solution. The following diagram depicts the solution architecture and process workflow of the solution.


This is just a learning exercise.  In the above diagram, we could have merged steps 2 to 6 in the Azure Function (webhook receiver) and pass the end result of step 6 to the Logic App.

Process Flow Steps

• When a new attachment record is created/received in CRM, it passes the RemoteExecutionContext to Azure Function (webhook receiver).
• The webhook receiver reads the attachment body from the entity attributes and calls the Logic App with the attachment body.
• The Logic App calls the cognitive services Vision API and passes the body of the attachment.
• The Vision API runs OCR on the image and passes the response back to Logic App.
• The response received by the Logic App has some unwanted data, so that response is passed to a second Azure Function (OCR Parser).
• The OCR parser transforms the response, assigns it to a lead object (class) and sends it back as JSON string.
• The Logic App parses the JSON string.
• The Logic App uses the 'Dynamics 365-Create a new record' action to create a new Lead record in D365.

Components

Azure Function as webhook receiver

The first step is to create an Azure function which will receive the RemoteExecutionContext from CRM when a new email attachment is created.This Azure function receives the CRM context, extracts the attachment body and sends it to a Logic App in "binary" format with a content-type of “application/octet-stream”. This is important as Azure Vision API does not receive the image contents in  “application/json” content-types.

Webhook registration in CRM

Register a webhook and add a step on the Create event (Post) of the Attachment entity using the Plugin Registration Tool.

 

Azure Function for OCR parsing

The OCR data has a lot of extra information like regions, lines and boundaries. The parser reads the required data from the response and assigns it to a Lead class. This class is then serialized into a JSON string and sent back to the Logic App.
I got OCR processing classes from another blog. I can't find a name of it now.

Logic App

1. Create a new Logic App and add an HTTP trigger. The URL of the request is used by the Azure function (webhook receiver).
   
2. Add an Optical Character Recognition (OCR) to JSON action. For this step register here to get the URL and key for “Computer Vision API”. Enter the connection information and after the connection is established, set Image Source to Image Content, and Image Content to the Body of the HTTP trigger.
   
   
   If successful, this step will return the OCR response.
3. Add an HTTP action and enter the required information as shown. The action will return the serialized JSON string of lead object.
   
4. Add a JSON Parser action that will parse the JSON string received in the last step.
   
5. Add a new Dynamics 365-Create a new record action. Assign the values based on the schema of the JSON parser.
   
6. Save the Logic App and test the solution.

Outcome

I tested the solution by sending an email with the following image attachment.


This is the result in D365.

Issues I faced

1. Originally, I wanted to use a Logic App trigger  (Dynamic 365 -When a record is created) to get the attachment body. For some reason, this trigger does not work for the attachment (activity mime attachment) entity.
2. Even if you can get the attachment using the Get Lists action by passing the Email entity guid, the result does not contain the body of the attachment. I don’t know if this is by design. In short you cannot get attachment body by using the Logic App D365 connector. This is why I had to use CRM webhooks and Azure functions.
3. Sometimes the OCR API does not recognize all the text on the image. For example, in the screen shot above letter “l” is missing in the email address (satnam@live.com versus satnam@ive.com).

Conclusion

I believe this integration scenario has massive potential. It can be used to:

1. Create records based on hand written notes.
2. Automatically link attachments to appropriate records based on some value on an image or in PDF documents.
3. Some federal department applications, banking loan applications and RMS (Road and Maritime Services) still use paper based applications. This can save them tons of data entry.
   

CRM- SQL server integration using Logic apps

It has been a long time since my last blog. I am thinking of doing a few more blogs about Logic Apps this year.  The best thing about Logic Apps are the number of connectors available out of the box to connect to different applications. With the enterprise integration account, Logic Apps can implement enterprise patterns and scenarios similar to BizTalk server.

This is the first blog of the series. In this blog, I will integrate D365 online with SQL server on premise using Logic Apps.

Solution components

Steps

This is very simple integration scenario. This Logic App will poll Dynamics 365 for new contact records and insert a row in the on premise SQL Server table named Customer.

1. Setup an on-premise Data Gateway. The following link provides the step by step instruction to setup a gateway.
   https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-gateway-connection
   Note: Consider latency while selecting the region for the gateway
2. Create a new Logic App. Check my old blog for screenshots of steps to get to the Logic App designer.
3. Select a Dynamics 365 connector and select Dynamics 365 - When a new record is created.
   
   
   
4. Enter the username and password to connect to your D365 organisation.
5. Select the Organization Name, Entity Name, Interval and Frequency as shown below. The fields Interval and Frequency are very important. It will define how often the Logic App will poll D365 for new records.
   
   
   
6. Add a New step and choose Add an action.
   
7. Choose SQL Server >> SQL Server - Insert row.
   
   
   
8. Enter the connection information as shown in the screen shot below. Make sure On Premise data gateway is checked and select the gateway created in step 1 of the blog.
   
   
   
9. If the connection is successful, select the name of the table and map the column names with the CRM field names.
   
   
   
10. The output of this action can be used to perform further action. For this blog, I am not doing anything with the output. Save the Logic App and test it by creating a record in CRM.
11. I ran some tests and here is the Run history of the app. After the initial run, the average time to process a record is sub 3 seconds, which is quite good considering my machine is Australia and Logic App is hosted in the US West region.
    
    
    
    
    
    
    
    
    
    
    
    
    
    

PowerApps and CRM optionsets

In this blog, I will walk you through the steps on how to work with the Dynamics CRM option sets in PowerApps. I will be extending the app I built in my last blog CRM and PowerApps revisited.

Problem Statement

The problem is that if you use out of the box Dynamics 365 Connector for PowerApps, it only retrieves the numeric value of the optionset. It does not return the text value of the optionsets. Below, I have added a Preferred Method of Contact field to the DetailScreen1.

As shown in the screenshot above, Preferred Method of Contact of 1 means nothing to a business user. It should display email, phone, mail, etc.

Solution

The Dynamics CRM connector for PowerApps does not display the text values of the optionsets or the metadata of the entities. So, there is no straight forward solution for this problem, but we can use something similar to a data maps table that contains what these numeric values mean.

Here are the steps:

Create a collection

We will create a collection (https://powerapps.microsoft.com/en-us/tutorials/create-update-collection/) to store the optionset values and the corresponding text.
1. Open the BrowseScreen1, choose On Start from the Actions Menu and add the following code.
Collect(Myc, {optionset:"PreferedContact", OptionValue:1,DisplayText:"Any"},{optionset:"PreferedContact", OptionValue:2,DisplayText:"Email"},{optionset:"PreferedContact", OptionValue:3,DisplayText:"Phone"},{optionset:"PreferedContact", OptionValue:3,DisplayText:"Fax"},{optionset:"PreferedContact", OptionValue:3,DisplayText:"Mail"})

The above code will create a collection Myc.

• DisplayText- displays the text of the optionset
• OptionValue- displays the numeric value of the optionset
• Optionset- displays the name of the optionset field. This field will be useful to the store the values for the multiple optionset fields in the same collection.
  
  

Display the Optionset Text

We will be using the PowerApps Lookup function to display the text for the optionset.

1. Open the DetailScreen1 and select the optionset field, as shown on the screen in the screenshot below.
   
   
2. Click on the Unlock the change properties.
3. Change the Data property to LookUp(Myc,((OptionValue=preferredcontactmethodcode)&&(optionset="PreferedContact")),DisplayText).
4. The DetailScreen1 will display the text associated with the optionset numeric value.
   
   

Editing the Optionsets in PowerApps

The above solution can show the text, but won’t let the user edit the values in the app. The solution to allow users to edit the optionsets values in PowerApps is little tricky.
Here are the steps to achieve this.

1. Add the Preferred Method of Contact optionset to EditScreen1.
2. Change the display for the field to Allowed Values as shown in the screenshot below. It will change the field to a dropdown field.
   
   
3. Click on the Unlock the change properties.
4. Select the dropdown field and change the Items property to collection Myc. In the dropdown properties pane, select the column to display in the Value field. By default, the first column of the collection is selected.
   
   
5. Change the Default property to LookUp(Myc,((OptionValue=preferredcontactmethodcode)&&(optionset="PreferedContact")),DisplayText).
6. Select the optionset card and change the Update property to DataCardValue4.Selected.OptionValue. This will tell the card to use the OptionValue field of the collection to update in CRM.
   
   
7. Save the application and test it.

I hope this helps.