Accessing Azure Blob storage from CRM Online Plugin

What is this about?

In this blog, we will see how to access Azure Blob storage and create document in CRM by reading a specific blob document in Azure.

Steps:

  1. Get the following details of the Azure Blob storage
    • Primary access key
    • Blob container name
    • File Name of the document
  2. Now we will write a sample plugin code which will read document from the Azure Blob storage and create an Annotation in CRM.

Since we cannot using external DLL in CRM online plugins, we are going to use the Web request to access the Azure blob storage. The HTTP web request has a bunch of headers along the above details to successfully access Azure blob storage.

For this, I have used a RestHelper and BlobHelper utility code files, which have all the operations of (a) making a web request and (b) performing blob operations.

The Helper files and the CRM plugin sample can be found in the below GitHub link:

GitHub: CRM Online Integration with Azure Blob

Using this we can get the document and create Annotation in CRM using the below code.

Please add your comments section and I would be happy to revert.

 #region Connect and fetch the data from Blob storage
 // Replace the below values with actual details from your Azure Blob storage
 string storageAccount = "blobstorageaccountname";
 string filename = "filenamehere"; // testdocument.pdf
 string containerName = "containernameHere"; //documents
 string storageKey = "primaryaccesskiyeofazureblobstorageaccount";


 BlobHelper blobHelper = new BlobHelper(storageAccount, storageKey);

 KeyValuePair<byte[], string> data = blobHelper.GetBlobResponse(containerName, filename);

 byte[] body = data.Key;
 string contentType = data.Value;
 #endregion

 #region Create Annotation in CRM
 string encodedData = System.Convert.ToBase64String(body);
 Entity Annotation = new Entity("annotation");
 Annotation.Attributes["objectid"] = new EntityReference(workOrder.LogicalName, workOrder.Id);
 Annotation.Attributes["objecttypecode"] = workOrder.LogicalName;
 Annotation.Attributes["subject"] = "Document from AX Integration";
 Annotation.Attributes["documentbody"] = encodedData;
 Annotation.Attributes["mimetype"] = contentType;
 Annotation.Attributes["notetext"] = "REST API - Sample document from AX.";
 Annotation.Attributes["filename"] = entity.GetAttributeValue<string>("cf_name");

 Guid annotation = service.Create(Annotation);
 #endregion

Dynamics CRM- Rollup with Custom Hierarchies

CRM Custom hierarchies are very useful feature for maintaining hierarchy among records and also provide nice visualization of the Hierarchies.

Learn more about hierarchies here: http://www.powerobjects.com/.015/03/30/microsoft-dynamics-crm-2015-custom-hierarchies/

With rollup fields and custom hierarchies have become even more useful to derive and visualize meaningful data in the hierarchy. In this blog, we will use how to leverage Rollup fields and Custom hierarchies to get proper data and avoid redundancy and custom calculations.

Problem statement:

Suppose you have a custom hierarchy of campaign, so a campaign can have multiple child campaigns. Each campaign has leads associated to it.

The requirement is to get the count of all the leads of the related child campaigns and also itself.

Solution:

If you want count of leads on the campaign, we can easily create a rollup field on Campaign to get the count of leads on Campaign. The rollup field will look like below:

rollupwithcustomhierarchy1

You can see the count of leads using the above rollup below on the campaign form:

rollupwithcustomhierarchy2

Get total lead count of child campaigns on Parent Campaign

Without hierarchy to achieve this, we would need to write a custom plugin or JavaScript to get the lead count of all the child campaigns and set it on the parent campaign.

With rollup using hierarchy, we can get this done without any calculations. See below the rollup definition for this. Note that we have now enabled “Use Hierarchy” in the rollup definition.

rollupwithcustomhierarchy3

This will get us the count of all leads including Child campaign as well.

See the form now with the updated rollup definition, the lead count is now “8” which is the sum of lead count of all child campaigns

rollupwithcustomhierarchy4

Conclusion:

Leveraging Calculated fields, rollup fields and custom hierarchies we can easily build good and simple solutions, avoid custom calculations and redundant fields.

CRM TimeZone issue using JavaScript

Problem: When we try to save a datetime field from an HTML TextBox (date-picker)  to CRM using oData in JavaScript, the datetime is always saved incorrectly.

Why This happens:

  • In JavaScript, date object is always read in local timezone. But CRM always accepts date Object in UTC from any client application. Then it shows Date and Time in the TimeZone selected by the User for CRM environment.
  • So, when we send this data to CRM using oData, it assumes that the date is in UTC timezone and thus we see incorrect time in CRM views and forms for the record.
  • Also we cannot convert to required TimeZone in JavaScript. We can convert Date object to UTC using .ToUTC() method, but it returns a Date string instead of Date Object and we need Date object to pass to oData.
But Don’t you worry, we have found the resolution !! 🙂 

In our example, we were trying to create an appointment from a HTML Form using a custom datepicker for setting Start date of the Appointment record.

IMG_04122014_150943

Resolution:

1. Read the local date in a string in JavaScript like below.

// textboxvalue = ‘2015/04/15 00:20’

var scheduledStart = new Date(textboxvalue + ‘ UTC’);

2. This will get the UTC date for the date specified. I am running the code from IST. So, the Scheduled Start will be :

“Thu Jan 15 2015 05:50:00 GMT+0530 (India Standard Time)”

3. When we pass this data to oData create/ update method, we will now see the correct date as expected. Below is the code to create an appointment using oData with required date conversions to see correct data

function createAppointment(currentAccount) {
“use strict”;
var appointment = new Object();
var scheduledStart = new Date(currentAccount.scheduledStart + ‘ UTC’);
var scheduledEnd = new Date(currentAccount.scheduledStart + ‘ UTC’);
scheduledEnd.setMinutes(scheduledStart.getMinutes() + 30);
appointment.Subject = “Sales Visit – ” + currentAccount[“Name”];
appointment.RegardingObjectId = { Id: currentAccount[“Id”], LogicalName: currentAccount.EntityName.toLowerCase(), Name: currentAccount[“Name”] };
appointment.ScheduledStart = scheduledStart;
appointment.ScheduledEnd = scheduledEnd;
var jsonEntity = window.JSON.stringify(appointment);
var createRecordReq = new XMLHttpRequest();
var ODataPath = Xrm.Page.context.getServerUrl() + “/XRMServices/2011/OrganizationData.svc”;
createRecordReq.open(‘POST’, ODataPath + “/” + ‘AppointmentSet’, false);
createRecordReq.setRequestHeader(“Accept”, “application/json”);
createRecordReq.setRequestHeader(“Content-Type”, “application/json; charset=utf-8”);
createRecordReq.send(jsonEntity);
JSON.parse(createRecordReq.responseText).d;
//Alert the Id of the created record

}

Hope this is helpful !!

The above resolution is successfully tested.. Please email/ comment if there is any other solution for this problem.

CRM Best Practices

This blog will have links of all the CRM Best Practices which means this will keep on updating as and when I find anything new or useful for CRM (read CRM Online)

  1. Workflow Wait Conditions: Best Practices-

    https://community.dynamics.com/crm/b/magnetismsolutionscrmblog/archive/2012/11/26/workflow-wait-conditions-best-practices-dynamics-crm-2011.aspx

  2. Open Modal Dialogs in CRM 2013

    http://mscrmmindfire.wordpress.com/2014/04/23/open-dialog-box-like-crm-2013/ 

  3. Attach Entity record notes/ attachment to Email in Custom Email Workflows

http://mahenderpal.wordpress.com/2014/01/20/sending-email-with-entity-attachment-from-notes/

More links to follow. Keep watching this space.


 

Any suggestion for such links/ blogs will be great. You can add it in comments.

Add Footers/ Summary in Google Grids

Problem Statement:

Google Grids do not have direct implementation for adding footers to the grid. So there is no direct provision for having a fixed total row in Google Charts- table (Google grids).

To achieve this, we need to add an extra row; add this row to the end of the table. We also need to ensure that the row is added even after pagination and sorting is performed on the grid.

So here we go:

1.      Declare this variable at top of your script

footer = [[‘1st column text’,1st_ColSpan],[‘2nd column text’,2nd_ColSpan],….];

 

Example :

footer=[[‘Total’,5],[‘1,234,567’,1]];

// <—assume our table have 6 columns

 

 

 

2.      Add this function to your script (anywhere)

function addFooter() {try {

if (document.getElementById(‘google-visualization-table-summaryFooter’)) return;

 

var tables = $(‘#parentID table’); // Parent container element of table. To                                                                                                             //find exact table

 

for (i = 0; i < tables.length; i++)

if (tables[i].className == ‘google-visualization-table-table’) {

var r = tables[i].insertRow(tables[i].rows.length);

r.id = ‘google-visualization-table-summaryFooter’ // This ID should be same as in the IF condition of the first line of this //function

var c;

r.className = ‘google-visualization-table-tr-head’;

for (j = 0; j < footer.length; j++) {

c = r.insertCell(j);

c.className = ‘google-visualization-table-th gradient’;

c.style.textAlign = (j == 0) ? ‘start’ : ‘right’;

c.innerHTML = footer[j][0]

c.colSpan = footer[j][1]

}

}

}

catch (ex) {

// Exception Logging

}

}

 

 

3.      Add these event listener before table.draw() method

 

google.visualization.events.addListener(table, ‘ready’, addFooter);

google.visualization.events.addListener(table, ‘sort’, addFooter);

google.visualization.events.addListener(table, ‘page’, addFooter);

 

Enjoy!!

You have your footer in the table as you want. Additionally you can apply custom styles based on the footer row ID to visually enhance the experience.

 

Comments

Google Charts and Grids

Why we use charts

We use graphics and charts because they help make any data

  • More effective
  • Interesting
  • Easy to understand
  • Easy to analyze and compare

They also help one to present data in a logical and a consistent way.

E.g. consider the example of Commission earned by individuals.

  • The Bar Chart below shows total commission earned by each individual. The commission amount shown here is grouped by individual
  • .

    Image

    FIGURE 1: BAR CHART SHOWING TOTAL COMMISSION EARNED BY INDIVIDUAL

  • The same data can be shown in a pie chart like below. Each pie represents the % of commission earned by an individual.
  • Pie chart also provides legend for each pie as shown in the right hand section of the figure below and a tool tip representing textual data.
Image

FIGURE 2: PIE CHART SHOWING TOTAL COMMISSION EARNED BY INDIVIDUAL

  • Google Tables/ Grids provides customizable and interactive grid which has features like:
    • Columns Sorting
    • Pagination
    • Selecting rows and formatting data (currency, hyperlinks, etc.)

The figure below shows details of all commissions earned by an individual. Data is not aggregated on Individual basis.

Image

FIGURE 3: GOOGLE GRID SHOWING COMMISSION EARNED DETAILS

 

Combining charts/ grids to show advanced data visualization:

  • We can combine the Pie chart/ bar chart with grid to show drill down in data.
  • In the below example, we have an event where a user on clicking any pie will see the details of the selected pie (a user in this case) in the grid.
  • So on clicking a user say Maria Campbell on Pie chart, the grid will show details of all the commissions earned by her along with other details like Commission Paid Status (Yes/ No), Commission amount and Commission paid date, etc.

 

Image

FIGURE 4: DATA DRILL DOWN USING CHARTS AND GRIDS

 

Google Charts Working Flow:

Image

FIGURE 5: GOOGLE CHARTS IMPLEMENTATION FLOW

 

  1. Client wants to visualize data in Charts/ Grids. Sends request to the application server.
  2. Application server fetches data from CRM or any other Data source.
  3. Application server processes the data.
    1. Convert Data to JSON format.
    2. Specify options for chart. These includes paging/ sorting/ custom formatting for Grids. Also for charts, options like x-axis details, y-axis details, legends, etc. can be specified.
  4. Call Google Visualization API (JS) which will accept the data and options as specified in Step 3.
  5. Google API now creates Charts/ Grids based on options and renders on the browser.

Why use Google Visualization API for Grids and Charts:

  1. Very simple steps to configure Grids and Charts.
  2. Wide variety of options are available for Charts: Bar charts, Area Charts, Pie charts, Tree maps, Donuts, Column charts, etc.

See the full list in details: https://developers.google.com/chart/interactive/docs/gallery

  1. Google API are cross-browser compatible, with support for all major browsers as Charts use SVG for drawing.
  2. Detailed documentation and examples are available
  3. Biggest reason: It is free for use. Refer Terms : https://developers.google.com/terms/

For Comments: