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.


<html>
<head>
    <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>
   
<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); 
       dataSet=myData.value;     
    } else {
     Xrm.Utility.alertDialog(this.statusText);
    }
   }
  };
  req.send();
     
   // Convert Json data into 2-d Array
    arrItems = [];    
  $.each(dataSet, function (index, value) {  
   arrItems.push(value.title);
   arrItems.push(value.ticketnumber);
     // 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" }          
        ]
    } );
}
   
</script>
</head>

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

</body>

</html>

 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.
AsyncWF

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.

Solution-

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 –
    CancelWFProgramatically
    b. Using Database Query-
    Note- You should create restore(Checkpoint) point first before working on database directly to rollback changes if needed.
    CancelWFDB
    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)
    CleanupScript
    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]
    WFsetting1
    Recommended value of ‘AsyncSdkRootDomain’ should be same as ‘ADSdkRootDomain’. Or you can put server name as value.
    WFsetting2
  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.

Thursday, May 3, 2018

Bad Characters Messing Up Your Migration to Microsoft CRM Dynamics


Our migration process typically consists of moving the source data into a staging SQL Server database prior to the actual migration to CRM. Among other reasons, this gives us a place to do data cleansing prior to the CRM migration.
We run into many common issues such as field length differences and data type mismatches that are often found during the data mapping process with the customer. One less common issue we encounter in testing a migration is that some characters in the source data are not supported in CRM when importing data via the API. There are certain non-printable characters that are supported such as carriage-return and line-feed however others like record separator [char 30] or vertical tab [char 11] often are not accepted when migrating data to CRM.
We've developed a common SQL framework we use to allow us to do some data analysis and clean-up of these invalid characters in our staging tables prior to doing our push of the data to CRM. In most cases we run the data migration without any cleansing and capture any failed rows into an error table where we keep the source system record id and the CRM API error message. From there we can determine if any entities had errors around invalid characters. Here is an example of what that error would look like. In our example we are using the KingswaySoft CRM Adapter for SQL Server Integration Services.
image1
Once we know what entities and fields have invalid characters we can start to build our cleanup routine from our base framework.

So which characters are going to cause us a problem?
In our research we found that the Microsoft Dynamics CRM API does not like asci characters below character number 32 (which is the space " " character). So we start with a list of 1 – 31 to represent potential bad characters. We also know that horizontal tab, carriage return, and line feed (CHAR(9), CHAR(13), and CHAR(10) respectively) are valid in CRM and should not be in this list of bad characters.
For the sake of examples, here is a sample script to spin up 10 ‘note’ records with potentially bad data in the NoteText field.
To create the list of bad characters, we used a Common Table Expression (CTE). The below script gives a numbered list containing the asci character values of known-bad characters called, ‘BadCharacters’.
From there it's a matter of writing a query that will join the known bad characters CTE with your stage table and have it review each character in the field that was reported as having bad characters in your error logging of your data migration. Here the SQL Cross Apply clause comes in handy to make this a simple process. In this example we are migrating notes into the CRM notes entity. I know from my error logging shown above that the notetext field in my stage table has some bad characters that CRM did not like. So I cross apply my BadCharacters table with my notes staging table and have it inspect the notetext field for bad characters (using the above CTE definition).
Here are the results of the above query on my data set. I can see exactly what records, what the raw value is currently in that field, what the bad character was reported and where in the string it exists.
image2
After I do my analysis and confirm that it’s acceptable to replace these characters an update script is run against my stage table. Here is my final script that I can include in my data migration process to swap out any bad characters with a blank string in my stage table prior to sending these records to CRM.
It should be noted that the CTE spins up 256 possible rows to cover every possible ascii character. In this case we know that we only want to do the cross apply on a subset of these potential values. But the Bad Characters CTE could be amended to include/exclude any ascii characters.

Hope it will help You!!.

Sunday, April 29, 2018

Calling Dynamics CRM 365 actions using the Web API (from outside CRM and inside CRM using JavaScript)

Calling unbound Dynamics CRM actions using the Dynamics 365 Web API


We first need to create an unbound action. With unbound I mean “not related to a Dynamics CRM entity”. The image below shows you what a simple unbound action could look like. This action does nothing else but checking the inbound parameter called “Age”. If this “Age” parameter (which will be send to the action using the Web API) is 18 or higher, the action will set the output parameter called “Valid” to “true” and will return this value. If this “Age” parameter is 17 or lower, the action will set the output parameter “Valid” to “false” and will return this value. The image below shows this action.

image


When we activate this action and browse to (or send a GET request to) “[ORGANIZATION URL]/api/data/v8.1/$metadata”, we will see that metadata about this new action will be exposed by CRM. The image below shows the exposed metadata about the new action:
image
My action is called “dys_CheckAge”, so for an unbound action, we could now send a POST to “[ORGANIZATION URL]/api/data/v8.1/dys_CheckAge”. We should of course send a JSON object as the body of the POST request, which in our case only has one parameter called “Age”. The object should like this:
{
  "Age": 12
}
If you now send a POST request using for example SOAPUI in which the “Age” property has a value of 12, you will see that the action will return the value “false”.
image
If you send a POST request (using for example SOAPUI) in which the “Age” property has a value of 21, you will see that the action will return the value “true”.
image
If you take a look at what actually goes over the wire (using for example Fiddler), you will see that it is just a simple POST request to the Web API with a simple body:
image
Especially in combination with a Custom Workflow Activity, this can be a very powerful thing to use which can be executed by itself. Calling this action cannot only be done from “inside” CRM using JavaScript, but this can of course also be done from another application (BizTalk / custom .NET application / etc).


Calling a bound Dynamics CRM actions using the Dynamics 365 Web API



Calling a bound Dynamics CRM action (an action which is related to a Dynamics CRM entity) works almost the same, but works slightly different. The image below shows you what a simple bound Dynamics CRM action could look like:
image
As you can see, the action is bound to the entity “Contact”, has 1 input parameter of type boolean and this input parameter is called “IsMale”. If the input parameter “IsMale” has the value “true”, the “Gender” field will be set to “Male” and the contact will be updated. If the input parameter “IsMale” has the value “false”, the “Gender” field will be set to “Female” and the contact will be updated. 
As soon as you activate this action and browse to (or send a GET request to) “[ORGANIZATION URL]/api/data/v8.1/$metadata”, we will see that metadata about this new bound action will also be exposed by CRM. The image below shows the exposed metadata about the new action:
image
The difference between calling an unbound action and a bound action is the URL. As said above, an unbound action can be access by “[ORGANIZATION URL]/api/data/v8.1/[ACTION-NAME]” and a bound action can be accessed / called by using the following URL: “[ORGANIZATION URL]/api/data/v8.1/ENTITYNAME(ENTITY-ID)/Microsoft.Dynamics.CRM.[ACTION-NAME]”. 
If you would now send a POST request (using for example SOAPUI) in which the “IsMale” property is set to “true”, you will see that the action will set the contact “Gender” to “Male”.
image
If you again would send a POST request (using for example SOAPUI) in which the “IsMale” property is set to “false”, you will see that the action will set the contact “Gender” to “Female”.
image
image

Calling a Dynamics CRM actions using JavaScript and the Dynamics 2016 Web API

A Dynamics CRM action can also contain business logic which can be triggered (called) using JavaScript and which helps the user (client-side) during data entry. One of the actions we created above was the “CheckAge” action which expects an “Age” property and returns the value “true” if the age is equal or above 18 (the JSON object returned will have a “Valid” property of type boolean). In all other cases it will return the value “false”. This action can be called using the following JavaScript:

"use strict";
var Dys = window.Dys || {};

Dys.ValidateField = function (context) {
    var data = {
        "Age": context.getEventSource().getValue()
    };

    //dys_CheckAge is the name of the custom unbound action in this case
    Dys.CallAction("dys_CheckAge", data, Dys.ValidateFieldUsingActionCallBack);
}

Dys.CallAction = function (query, data, callback) {
    var url = Xrm.Page.context.getClientUrl() + "/api/data/v8.0/" + query; 

    var req = new XMLHttpRequest(); 
    req.open("POST", url, true);
    req.setRequestHeader("Accept", "application/json"); 
    req.setRequestHeader("Content-Type", "application/json; charset=utf-8"); 
    req.setRequestHeader("OData-MaxVersion", "4.0"); 
    req.setRequestHeader("OData-Version", "4.0"); 
    req.onreadystatechange = function () { 
        if (this.readyState == 4) { 
            req.onreadystatechange = null; 
            if (this.status == 200) {
                callback(req.response);
            } else { 
                var error = JSON.parse(this.response).error; 
                alert(error.message); 
            } 
        } 
    };
    req.send(window.JSON.stringify(data));
}

Dys.ValidateFieldUsingActionCallBack = function (response) {
    alert("The value is: " + JSON.parse(response).Valid);
}




The JavaScript function “Dys.ValidateField” will be called whenever you change a custom field on a (Contact) form. This method will create a JSON object with 1 property called “Age” and then this function will call the JavaScript function “Dys.CallAction”, which will execute the Dynamics CRM action called “dys_CheckAge”. When this “Dys.CallAction” JavaScript function is finished, it will execute the JavaScript (callback) function “Dys.ValidateFieldUsingActionCallBack”, which will show the value which is returned by the Dynamics CRM action (property “Valid” of the response JSON object).

Summary

Although this example shows very simple scenario’s, the possibilities are clear:
  • You are able to execute pieces of reusable (business) logic from inside or outside CRM as if it were a “custom service”.
  • Althoug creating and updating CRM data is possible without using the Dynamics CRM actions as well (by simply sending a POST the the Web API), using the Dynamics CRM actions allow you to apply business logic before anything is created / updated.
  • Complex business logic can be executed if you create a Custom Workflow Activity (CWA) which is used inside your Dynamics CRM action.
  • A Dynamics CRM action also enables you to decouple the inside of CRM from the outside of CRM. With this I mean that you can keep your input parameters the way ther are, but do the translation (to a changed fieldname for example) in the action. This way, the outside world can reuse your action the way they are used to, while you shuffle things around within Dynamics CRM.

Tuesday, April 24, 2018

IntelliSense in Jscript/TypeScript file – Dynamics 365

In this article, lets see how to configure intellisence with XRM syntaxes in your Jscript/TypeScript files.
To enable IntelliSence, you need to install xrm npm package to your Visual Studio.
Steps to install NPM package:
  • To install npm package, you need to download and install a Visual Studio extension : Package Installer
Intelli - 5
  • Post installation, from your Visual Studio, select ‘Quick Install Package’ from Menu
Intelli - 6
  • From the popup,
    • Select ‘npm’ in the dropdown
    • In the textbox, type ‘@types/xrm
    • Click on ‘Install’
Intelli - 7
  • Post package installation, refresh the project and you should see a new folder ‘node_modules
    • Note: If you dont get the ‘node_modules’ folder, check your project’s physical location and add the folder to your Visual Studio project.
Intelli - 8
  • In above screen, ‘TypeScript’ is my project name and post above steps, I got the ‘node_modules’ project.
Enable IntelliSence in your Jscript/TypeScript file:
  • Open your script file
  • Drag and drop the ‘index.d.ts’ file from ‘node_modules’ folder on to your script file.
    • Alternatively you can also this syntax
      • /// <reference path=“../node_modules/@types/xrm/index.d.ts />
Intelli - 1
  • Start typing ‘Xrm’ and you should see the syntaxes.
Notes:
  • Those who are wondering what is a npm, it is a package manager for the JavaScript programming language.
  • www.npmjs.com hosts thousands of free packages to download and use.

Saturday, April 21, 2018

POWER BI INTEGRATION WITH MICROSOFT DYNAMICS 365 Out of box

Today i am going to show and explain you how to integrate Dynamics 365 with Power BI Out of box..

First of all you need to enable the integration so go to Dynamics 365 > Settings > Administration > System Settings >Select  Reporting Tab



That's all we need to configure in the Dynamics 365..

We are going to connect to one of the Content Packs made for Dynamics 365 inside Power BI, so we need the OData feed from D365. Go to Settings-> Customizations ->Developer Resources:


From the above image copy the url..upto the .com...

Login to the Power BI and Login with the same account as you logged into Dynamics 365.

Open up the side bar menu and go to the Get Data



Under the Content Pack Library:

Select Services:





Then you will be prompted with the below page and search for Dynamics 365 .. and select the Customer Service Analytics for Dynamics 365..




You will be prompted with the following page and click on the get in now button..




Again new pop up will come up and place the url copied from the developer resource of Dynamics 365

https://yourcompany.api.crm11.dynamics.com



Click on Next button then you will be prompted with the below pop up ..
In the pop up , Select Authenticated Method =  " oAuth2 " and the click Sign in.




Then you will get sign in page and select the login credentials that you have used for the Dynamics 365 and Power BI



Click on the credentials then ...



After few minutes you will be prompted with beautiful dashboard..


Yahoo!.. we have powerful and very useful dashboard is ready to view..

Then head back to Dynamics 365 > Services > Dashboard.. then Click on the "NEW" dropdown..
you will be prompted with "Power BI Dashboard" select it..





After clicking on the Power BI Dashboard ..


Then check the box for enable for mobile and click on save button..



Here you go .. you can see "Customer Service Analytics for Dynamics 365 Dashboard.. at the moment on the current dashboard 10 underlying reports and if you want to go back to Power BI, you can click on the top right side button " OPEN IN POWER BI".. then you will in POWER BI again..

This is very cool....


 if you want to share this dashboard with other colleagues in your organization you’ll have to share the dashboard in PowerBI. Then the users can import it to their personal dashboard inside D365, so security is pretty much handled by PowerBI. The content packs made by Microsoft gives you a great starting point for reports, I recommend  every one of them

As always, if you need any help,

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...