Tuesday, October 23, 2018

Extracting attachments from an incoming email, create case, and add attachments to as notes


One of my client's using Dynamics CRM Online 2016 Update had a requirements to create Case (renamed to Enquiry) records when an email arrives to their info@*****.com mailbox. Using OOTB Automatic Record Creation and Update Rules feature a Case is created and the email is visible under Activities.
Our client felt that the user experience wasn't great and the original Email was hard to find with all the other activities got created afterwards.


The solution was to extract the attachments from the email message and create notes against the Case record during the case creation process. Below are the steps used to implement the solution.
  1. Create a custom workflow activity to extract the attachments and create notes
  2. Create a workflow which includes the above custom workflow activity as a Step
  3. Create an Automatic Record Creation and Update Rule
  4. Specify Record Creation and Update Details which calls the workflow created in step 2


Create a custom workflow activity to extract the attachments and create notes

Below is the source code of the custom workflow activity.
using System;
using System.Activities;
using System.Linq;

using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Xrm.Sdk.Workflow;

namespace Dyn365Apps.CRM.Workflow
    public class ExtractAttachmentsFromEmailAndCreateNotes : CodeActivity
        public InArgument<EntityReference> receivedEmail { get; set; }

        public InArgument<EntityReference> enquiry { get; set; }

        protected override void Execute(CodeActivityContext context)
            var trace = context.GetExtension<ITracingService>();

                var serviceFactory = context.GetExtension<IOrganizationServiceFactory>();
                var service = serviceFactory.CreateOrganizationService(Guid.Empty); //Use current user's IDif (service != null)
                    trace.Trace("Organization Service Created");

                // Get Attachment Count
                trace.Trace("Get Attachment Count");
                var rem = receivedEmail.Get(context);
                Entity email = service.Retrieve(rem.LogicalName, rem.Id, new ColumnSet("attachmentcount"));
                int atc = (int)email["attachmentcount"];
                trace.Trace("Attachment count = " + atc.ToString());

                if (atc > 0)
                    // Get all attachments
                    QueryExpression queryAtt = new QueryExpression("activitymimeattachment");
                    queryAtt.ColumnSet = new ColumnSet(newstring[] { "activityid", "attachmentid", "filename", "body", "mimetype", "subject" });
                    queryAtt.Criteria = new FilterExpression();
                    queryAtt.Criteria.FilterOperator = LogicalOperator.And;
                    queryAtt.Criteria.AddCondition(new ConditionExpression("activityid", ConditionOperator.Equal, email.Id));
                    EntityCollection eatt = service.RetrieveMultiple(queryAtt);
                    var entities = eatt.Entities;

                    trace.Trace("Entities count = " + entities.Count());

                    foreach (var ent in entities)
                        trace.Trace("Inside the for loop");
                        trace.Trace("Attributes count = " + ent.Attributes.Count());

                        // Instantiate an Annotation object.
                        Entity annotation = new Entity("annotation");

                        if (ent.Attributes.Contains("subject"))
                            trace.Trace("subject = " + ent.Attributes["subject"].ToString());
                            annotation["subject"] = ent.Attributes["subject"].ToString();
                            trace.Trace("subject not found");
                            annotation["subject"] = "Undefined";
                            trace.Trace("filename = " + ent.Attributes["filename"].ToString());
                            annotation["filename"] = ent.Attributes["filename"].ToString();
                            trace.Trace("filename not found");
                            annotation["filename"] = "Undefined.txt";

                        if (ent.Attributes.Contains("mimetype"))
                            trace.Trace("mimetype = " + ent.Attributes["mimetype"].ToString());
                            annotation["mimetype"] = ent.Attributes["mimetype"].ToString();
                            trace.Trace("mimetype not found");
                            annotation["mimetype"] = "plain/text";

                        if (ent.Attributes.Contains("body"))
                            annotation["documentbody"] = ent.Attributes["body"];
                        trace.Trace("objectid = " + enquiry.Get(context).Id.ToString());
                        annotation["objectid"] = enquiry.Get(context);
                        annotation["objecttypecode"] = 112; // Case// Create a Note with the attachment
            catch (Exception ex)
                trace.Trace("ex.Message = {0}", ex.Message);
                trace.Trace("ex.StackTrace = {0}", ex.StackTrace);

Create a workflow which includes the above custom workflow activity as a Step

  • Create a workflow and name it Extract Attachments from Email - Create Notes - Attach to Enquiry
  • Set the Entity as Email
  • Enable workflow to run as a child process.
  • Add the custom workflow activity ExtractAttachmentsFromEmailAndCreateNotes as a step.
  • Custom workflow activity requires two inputs
  1. Email
  2. Case (Enquiry)
  • Activate the workflow.

Create an Automatic Record Creation and Update Rule

  • Navigate to Settings > Service Management and click on Automatic Record Creation and Update Rules.
  • Create a new Record Creation and Update Rule.

Specify Record Creation and Update Details which calls the workflow created in step 2

  • Create a new Record Creation and Update Details record.
  • Configure the conditions and actions.
  • Under Specify Other Actions, add a new step to Start a Child Workflow.
  • Select the Workflow created in Step 1
That's it (Assumption: The mailbox is configured to receive emails).
Now send an email with attachments to the mailbox.
The attachments in the Email Message are now under Case record's Notes section.

Enabling Auditing to monitor User's Security Role changes

I got an call from a client who recently went live with a system we built. Some of their user's security roles were getting removed and they wanted to find out what's going on.
Dynamics 365 (CRM) can record association and disassociation of security roles of a user as part of the OOTB Auditing capability.
Here are the instructions I provided my client to resolve the issue.

Enable Auditing

First step is to make sure Auditing feature is still enabled as we have configured.
Navigate to Settings > Administration > System Settings
Click Auditing Tab
Under Audit Settings, tick the Start Auditing checkbox
If you would like to monitor user access, tick Audit user access checkbox as well.
Click OK.

Enable Auditing for Security Roles

Next step is to make sure Auditing is enabled for Security Roles entity.
Navigate to Settings > Customizations > Customize the System
New Window opens
Expand Entities from the left pane
Click on Security Roles entity (Please wait for few seconds for it to load)
Tick Auditing checkbox.
Click Save.
Click Publish.

Viewing Audit Logs

Finally, to view the audit logs,
navigate to Settings > Auditing > Audit Summary View
You’ll see the list of audit entries
Double click to open the record to see more details.
That's it. 

Sunday, September 9, 2018

Code Snippet for making all form fields read only

function makeFieldsReadOnly() {
    var controls = Xrm.Page.ui.controls.get();
    for (var i in controls) {
        var control = controls[i];
        if (control.getDisabled && control.setDisabled && !control.getDisabled()) {

Friday, June 8, 2018

Key Points for Editable Grid in Dynamic 365

Limitations of Editable Grid

Before working with it, we need considering these notes for the limitation.
For field
  • Composite fields will not working in editable grid, such as address, full name fields.
  • State, state code will not be effect to change directly from enable grid.
  • Customer fields.
  • Party list fields (often being used in activity of Crm).
  • Fields from related entities
  • Field Security. Field
For form
Fields are set read-only on the form are not effected to editable grid.

Consideration of Editable Grid

  • Xrm.Page is not worked on editable grids.
  • OnRecordSelect, OnSave, OnChange events are support in editable grid.
  • Nested grid does not work in web and tablet, only work for mobile client.
  • Business rules is support for editable grid:
    • Set field value
    • Set business required
    • Lock/Unclock field
    • Set default value

Tuesday, May 29, 2018

Create a custom Grid in MS Dynamic CRM using Jquery DataTable

Sometime we need to show our data in table format. We should use Jquery DataTable to show the data in sub-grid (Table) format. 

Copy the below code and paste this code in your editor. And change the code as per your need. Here i will create a DataTable of Case Entity.

    <title>MS Dynamic CRM</title>
    <script src="ClientGlobalContext.js.aspx" type="text/javascript"></script> 
 <link rel="stylesheet" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css">
 <link rel="stylesheet" href="https://cdn.datatables.net/select/1.2.5/css/select.dataTables.min.css">
 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
 <script src="https://code.jquery.com/jquery-1.12.4.js"> </script>
 <script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"> </script>

  var dataSet;
  var arrData = [];

$(document).ready(function() { 
 // Get the data in Json format, Change the URL as per your need.
    var entityName ="incident";    // This is the Entity name of Case.
  var  url = window.parent.Xrm.Page.context.getClientUrl() + "/api/data/v8.2/" + entityName +"s?$select=title,ticketnumber,prioritycode";      
  var myData = []; 
  var req = new XMLHttpRequest();
  req.open("GET",url, false);
  req.setRequestHeader("OData-MaxVersion", "4.0");
  req.setRequestHeader("OData-Version", "4.0");
  req.setRequestHeader("Accept", "application/json");
  req.setRequestHeader("Content-Type", "application/json; charset=utf-8");
  req.setRequestHeader("Prefer", "odata.include-annotations=\"*\"");
  req.onreadystatechange = function() {
   if (this.readyState === 4) {
    req.onreadystatechange = null;
    if (this.status === 200) {    
       myData = JSON.parse(this.response); 
    } else {
   // Convert Json data into 2-d Array
    arrItems = [];    
  $.each(dataSet, function (index, value) {  
     // arrItems.push(value.prioritycode);   or
   arrItems.push(value["prioritycode@OData.Community.Display.V1.FormattedValue"]) ;  // For OptionSet value    
   arrData.push(arrItems);   // Push The Values Inside the Array to Create 2-D Array
   arrItems = [];          
  table(); // Call a table function to create table.  

function table() { 
 $('#customdatatable').DataTable( {
        data: arrData,
        columns: [
            { title: "Title" },  // Change the column name as per your need.
   { title: "Ticket Number" },
   { title: "Priority" }          
    } );

<body style="word-wrap: break-word;">
 <table id="customdatatable" class="display" width="100%"></table>



 Create a new HTML Web-resource and Upload the code in this web-resource and check the table.

Asynchronous Processes/Wrokflows Stuck in InProgress/Waiting status in MS Dynamic CRM

CRM developer/User once in a life faces the issue regarding Asynchronous Processes stuck in same status i.e. InPrgress/Waiting/Pausing/Canceling.You can see the system job status is not changing.

The Reason-

The main reason behind this issue  –
  1. Many jobs are in waiting status.
  2. Asyncoperationbase table become full due to many succeeded/canceled jobs occupied space.
  3. Asynchronous processes settings are not proper.
  4. The asynchronous workflows are not configured properly.


The solution for this issue –
  1. Very first step is to restart the  Microsoft Dynamics CRM Asynchronous Processing Service. This might work in many cases.
  2. Many Jobs are in waiting status->
    you can update the job status to canceled and completed by creating console application or from database-
    a. Create the console and use the script –
    b. Using Database Query-
    Note- You should create restore(Checkpoint) point first before working on database directly to rollback changes if needed.
    You can use “where statusCode=10 –Waiting”.
  3. Asyncoperationbase table become full due to many succeeded/canceled jobs occupied space –
    You need to cleanup the database by deleting the succeeded and canceled jobs-
    Make sure that only the following Async operation types are deleted if the state code of the types is 3 and the status code of the types is 30 or 32:
    • Workflow Expansion Task (1)
    • Collect SQM data (9)
    • PersistMatchCode (12)
    • FullTextCatalogIndex (25)
    • UpdateContractStates (27)
    • Workflow (10)
    If script took very long time then you should stop the script and rebuild the indexes for AsyncOperationBase as well as PrincipalObjectAccess tables. And run the script again.
  4. You can check if the values are optimal-
    • AsyncItemsInMemoryHigh
    • AsyncItemsInMemoryLow
    • AsyncStateStatusUpdateInterval
    • AsyncMaximumThreadsPerCPU
    • AsyncSelectInterval
    • AsyncSelectParallelism
    • AsyncThrottlingConfiguration
    Also you can check the ‘AsyncSdkRootDomain’ setting from  [MSCRM_CONFIG].[dbo].[DeploymentProperties]
    Recommended value of ‘AsyncSdkRootDomain’ should be same as ‘ADSdkRootDomain’. Or you can put server name as value.
  5. And Finally you can check your asynchronous work flow logic.
    There are many possibilities the workflows are stuck due to internal logic.
You have to restart the  Microsoft Dynamics CRM Asynchronous Processing Service after executing any above step.

Get files of last hour in Azure Data Factory

  Case I have a Data Factory pipeline that should run each hour and collect all new files added to the data lake since the last run. What is...