I recently moved this site from BlogEngine.NET to Orchard, and I decided I wanted to offer some basic HTML formatting (bold, italic, hyperlinks, etc.) functionality for comments, but I didn't want to do any significant amount of coding. After some research I realized that Markdown syntax would be the easiest way to let my visitors add HTML to their comments, and I could enable it with just a few bits of JavaScript.

This solution involves three parts:

1. Enable Markdown->HTML conversion on the client side using JavaScript.
2. Add a JavaScript function to trigger the conversion when comments are displayed to site visitors.
3. Update the Orchard ListOfComments shape to support the scripts in steps 1 and 2.

Enabling Markdown->HTML conversion

First, we need to enable Markdown->HTML conversion in the browser via JavaScript. For this, I chose PageDown, which is what StackOverflow uses for Markdown previewing. The PageDown project includes three JavaScript files:

1. Markdown.Converter.js - This does the Markdown->HTML conversion.
2. Markdown.Sanitizer.js - This lets you whitelist the HTML tags you want to allow. For example, you don't want to render script tags.
3. Markdown.Editor.js - This enables a live preview of the Markdown->HTML conversion. I decided not to use this now because I am am only allowing extremely basic functionality like hyperlinks, lists, bolds and italics. Maybe someday I will revisit this.

Download the Markdown.Converter.js and Markdown.Sanitizer.js from the PageDown project source. You may want to change the list of HTML tags that you want to render. If so, open the Markdown.Sanitizer.js file and find the following line to remove or add tags as appropriate.

var basic_tag_whitelist = /^(<\/?(b|blockquote|code|del|dd|dl|dt|em|h1|h2|h3|i|kbd|li|ol|p|pre|s|sup|sub|strong|strike|ul)>|<(br|hr)\s?\/?>)$/i;

Once you have made any changes to the Markdown.Sanitizer.js file, update your theme to include them in your Orchard-generated pages. In my case, I added them to my theme's Scripts directory and then added these two lines to my Layout.cshtml file:

 Script.Include("Markdown.Converter.js");  
 Script.Include("Markdown.Sanitizer.js");

Rendering and displaying HTML-formatted comments

Once you have the PageDown scripts added to your theme, you will have the capability to render HTML from Markdown, but you need to actually turn it on for comments. I found the easiest way to do this was to use JavaScript to replace the text of the body of each comment with the PageDown-generated HTML when the comment list is displayed. With jQuery this is simple. Just write a function that does the transformation for every P element of a specific class. Here's the code:

$(document).ready(function () {  
 //var converter = new Markdown.Converter();  
 var converter = Markdown.getSanitizingConverter();  
 $('p.commentbody').each(function (ix, commentbody) {  
 var convertedhtml = converter.makeHtml($(commentbody).html());  
 $(commentbody).html(convertedhtml);  
 })  
});

I created a separate JavaScript file to hold that function that I call CommentMarkdownApply.js. This file also is placed in my theme's Scripts folder and loaded via a Script.Include call in the Layout file.

Updating the shape

Finally you need to wrap the comments' body text with the "commentbody" class that is used in the CommentMarkdownApply script and remove the automatic conversion of newlines to
tags that Orchard does when it renders comments. Individual comments are rendered via the ListOfComments shape, so you can create or update an alternate ListOfComments.cshtml file with the changes. To override the standard comment body display, you would just change this

<p class="text">@(new MvcHtmlString(Html.Encode(comment.Record.CommentText).Replace("\r\n", "<br />\r\n")))</p>

to

<p class="commentbody">@(new MvcHtmlString(Html.Encode(comment.Record.CommentText)))</p>

Upload all your new theme files and you're done. Happy blogging!

Last month I wrote a post about how to create a web resource dialog "launcher" that you can embed in a CRM form (both classic and updated modes) with JavaScript and an OData query. In today's post, I will show how to do the same thing using a FetchXML query. There are four changes you need to make to the web resource from the previous post.

ClientGlobalContext.js.aspx

First, because we're working with FetchXML, you need to add a reference to ClientGlobalContext.js.aspx JavaScript to your page head.

Add the buildFetchRequest function

You need to add a function to build a full SOAP request from a FetchXML query. This is the same buildFetchRequest function I have used in previous FetchXML-related posts.

function buildFetchRequest(fetch) {  
/// <summary>  
/// builds a properly formatted FetchXML request  
/// based on Paul Way's blog post "Execute Fetch from JavaScript in CRM 2011"  
/// http://blog.customereffective.com/blog/2011/05/execute-fetch-from-javascript-in-crm-2011.html  
/// </summary>  
var request = "<s:Envelope xmlns:s=\"http://schemas.xmlsoap.org/soap/envelope/\">";  
request += "<s:Body>";   
  
request += '<Execute xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services">' +   
'<request i:type="b:RetrieveMultipleRequest" ' +   
' xmlns:b="http://schemas.microsoft.com/xrm/2011/Contracts" ' +   
' xmlns:i="http://www.w3.org/2001/XMLSchema-instance">' +   
'<b:Parameters xmlns:c="http://schemas.datacontract.org/2004/07/System.Collections.Generic">' +   
'<b:KeyValuePairOfstringanyType>' +   
'<c:key>Query</c:key>' +   
'<c:value i:type="b:FetchExpression">' +   
'<b:Query>';  
  
request += CrmEncodeDecode.CrmXmlEncode(fetch);   
  
request += '</b:Query>' +   
'</c:value>' +   
'</b:KeyValuePairOfstringanyType>' +   
'</b:Parameters>' +   
'<b:RequestId i:nil="true"/>' +   
'<b:RequestName>RetrieveMultiple</b:RequestName>' +   
'</request>' +   
'</Execute>';   
  
request += '</s:Body></s:Envelope>';   
return request;  
}  

Update the getDialogList function

You also need to update the getDialogList function to build and execute a FetchXML query instead of an OData query. Here is the updated function:

//function to retrieve the dialogs  
function getDialogList() {  
var dialogname = dataObj["dialogname"];  
var entitytypename = dataObj["entitytypename"];   
  
//only execute query if the record already exists  
if((recordId != null) && (recordId != '')) {  
  
//no point if entitytypename is provided  
if((entitytypename != null) && (entitytypename != ''))  
{  
//path to CRM root  
var server = window.location.protocol + "//" + window.location.host;  
  
//full path to CRM organization service - you may need to modify this depending on your particular situation  
var path = server + "/XRMServices/2011/Organization.svc/web";  
var fetchQuery = '<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">';  
fetchQuery += '<entity name="workflow">';  
fetchQuery += '<attribute name="workflowid" />';  
fetchQuery += '<attribute name="name" />';  
fetchQuery += '<attribute name="category" />';  
fetchQuery += '<attribute name="primaryentity" />';  
fetchQuery += '<attribute name="type" />';  
fetchQuery += '<attribute name="description" />';  
fetchQuery += '<order attribute="name" descending="false" />';  
fetchQuery += '<filter type="and">';  
fetchQuery += '<condition attribute="name" operator="like" value="'+dialogname+'%" />';  
fetchQuery += '<condition attribute="type" operator="eq" value="1" />';  
fetchQuery += '<condition attribute="category" operator="eq" value="1" />';  
fetchQuery += '<condition attribute="primaryentityname" operator="eq" value="'+entitytypename+'" />';  
fetchQuery += '</filter>';  
fetchQuery += '</entity>';  
fetchQuery += '</fetch>';  
  
var fetchRequest = buildFetchRequest(fetchQuery);  
  
$.ajax({  
type: "POST",  
dataType: "xml",  
contentType: "text/xml; charset=utf-8",  
processData: false,  
url: path,  
data: fetchRequest,  
//complete: function(xhr, status) {  
// console.log(xhr.responseText);  
//},  
beforeSend: function( xhr ){  
xhr.setRequestHeader(  
"SOAPAction",  
"http://schemas.microsoft.com/xrm/2011/Contracts/Services/IOrganizationService/Execute"  
); //without the SOAPAction header, CRM will return a 500 error  
}  
}).done(function(data) {  
//if successful, pass returned xml to the display function  
generateLauncherMenu(data);  
}).fail(function(jqXHR, textStatus, errorThrown ) {  
//if unsuccessful, generate an error alert message  
alert( "Request failed: " + textStatus + "\n" + errorThrown );  
});  
}  
}  

Update the generateLauncherMenu function

Finally, you need to update the generateLauncherMenu function to accept an XML input parameter and parse/process accordingly. Here is an updated version that, as in my previous example, generates buttons to launch the dialogs:

//callback function to parse the response and build the menu output  
function generateLauncherMenu(xml) {  
$(xml).find("a\\:Entity").each(function() {  
var workflowid, primaryentity, name, paramString;  
//inner loop  
$(this).find("a\\:KeyValuePairOfstringanyType").each(function() {  
var xmlElement = $(this);  
var key = xmlElement.find("b\\:key").text();  
var value = xmlElement.find("b\\:value").text();  
switch(key) {  
case "workflowid":  
workflowid = value;  
break;  
case "primaryentity":  
primaryentity = value;  
break;  
case "name":  
name = value;  
break;  
}  
paramString = "\"" + workflowid + "\",\"" + primaryentity + "\",\"" +recordId + "\"";  
});  
$('#launchDiv').append( "<button onclick='openDialog("+paramString+")'>Launch " + name + "</button>&nbsp;&nbsp;" );  
});  
}

Wrapping up

Once those four changes are made, this web resource will behave exactly like the OData version.

I love Dynamics CRM dialogs. In fact, I think they are one of the best features of CRM 2011. What I don't like about dialogs is how the user has to run them when working with a entity record. On a "classic" mode form, the user has to go to the ribbon, click start dialog and then find the dialog in a list. If there are lots of dialogs for that entity type, the user then has to spend a lot of time looking, change the view or search. That experience is still better than on an "updated" mode forms where the user can't run dialogs at all without switching to classic mode. It is possible to launch a dialog via a URL from a web resource embedded in a CRM form using the format described here, but that approach requires you hardcode the GUID for the dialog you want to open. In this post I will show how to create a web resource dialog "launcher" that you can embed in a CRM form (both classic and updated modes) with JavaScript and an OData query.

Originally when I started looking at this, I was thinking about just a single dialog launch button, but then I remembered the "macros" system I created originally for CRM 3, and I realized that a dynamic dialog menu would achieve a lot of the same goals without anywhere near as much custom code. That being said, the approach I am going to show will allow you to create a menu that displays either a single dialog or multiple dialogs depending on exactly how you want to make them available to your end users.

Retrieving the dialogs

First, we have to retrieve the dialogs. Normally, I am a big fan of using FetchXML for all my multi-record CRM data retrieval, but I decided to get the list of dialogs via OData because the query is extremely simple, and I doubt anyone will run into a situation where the number of dialogs to retrieve will exceed the OData maximum limit.

Here is an OData query for retrieving an account-related dialog named Lucas Demo Account Dialog:

/WorkflowSet?$filter=Category/Value eq 1 and Type/Value eq 1 and StatusCode/Value eq 2 and PrimaryEntity eq 'account' and Name eq 'Lucas Demo Account Dialog' &$select=Name, WorkflowId, Description, Category, Type, PrimaryEntity&$orderby=Name

Let's take a closer look at the query parameters.

1. The workflow set contains workflows and dialogs, so we use Category/Value eq 1 to get only dialogs.
2. In addition to containing workflow and dialog definitions (and templates), the workflow set contains a separate entry for each activation. That is if you create and activate a dialog, then deactivate / edit / reactivate, etc., you will see the same dialog name multiple times in the results. This is why we use Type/Value eq 1 to get only the actual definition.
3. A user can't execute a draft dialog, so we use StatusCode/Value eq 2 to retrieve only published dialogs.
4. PrimaryEntity eq 'account' is used to retrieve only dialogs related to the account entity.
5. Name eq 'Lucas Demo Account Dialog' returns only dialogs with the name of Lucas Demo Account Dialog.

That query is fine if you only want users to be able to launch a single dialog from your menu, but what if you want to have multiple dialogs? The easiest way to do that is to remove the Name parameter, so a query for all account-related dialogs would look like this:

/WorkflowSet?$filter=Category/Value eq 1 and Type/Value eq 1 and StatusCode/Value eq 2 and PrimaryEntity eq 'account' & $select=Name, WorkflowId, Description, Category, Type, PrimaryEntity&$orderby=Name

This is OK, but there's a better approach that will allow for retrieving single or multiple records without having to use a different query format. Here's the query:

/WorkflowSet?$filter=Type/Value eq 1 and Category/Value eq 1 and StatusCode/Value eq 2 and PrimaryEntity eq 'account' and startswith(Name, 'XXX') &$select=Name, WorkflowId, Description, Category, Type, PrimaryEntity&$orderby=Name

Using the "startswith" operator gives you a lot more control. If you do startswith(Name, ''), you get everything. If you do startswith(Name, 'Lucas Demo Account Dialog'), you get just the single dialog (assuming you only have one dialog with that name/entity combination). This approach also allows you to show a subset of dialogs. Imagine you have two kinds of account dialogs, sales and service. If you prefix your sales dialogs with SALES- and your service dialogs with SVC-, you can filter the dialogs to build a service-only menu using the following query.

/WorkflowSet?$filter=Type/Value eq 1 and Category/Value eq 1 and StatusCode/Value eq 2 and PrimaryEntity eq 'account' and startswith(Name, 'SVC-')&$select=Name, WorkflowId, Description, Category, Type, PrimaryEntity&$orderby=Name

At this point, we have everything we need from an OData query perspective to write a separate web resource for every entity/functional combination, but I think it's better to use a generic web resource that takes the entity and name parameters via a query string. Unfortunately JavaScript doesn't have native query string parsing capability, so we need to use a custom function to extract the query string values. This is based on something I found on Stack Overflow.

function parseQueryString(qs) {  
var result = {}, keyValuePairs = qs.split('&');  
  
keyValuePairs.forEach(function(keyValuePair) {  
keyValuePair = keyValuePair.split('=');  
result[keyValuePair[0]] = keyValuePair[1] || '';  
});  
  
return result;  
}  
  
//For IE8- support include this tiny Array.forEach* polyfill:  
if ( !Array.prototype.forEach ) {  
Array.prototype.forEach = function(fn, scope) {  
for(var i = 0, len = this.length; i < len; ++i) {  
fn.call(scope, this[i], i, this);  
}  
}  
}

We then decode the querystring like this:

var queryObj = parseQueryString(location.search.slice(1));

Because we will be passing the dialog name and entity name to the web resource as custom parameter data, we have to URI decode the "data" object and then parse it. We can then pass the URI-decoded data object to the parseQueryString function to parse it like this:

var dataObj = parseQueryString(decodeURIComponent(queryObj["data"]));

With the query string fully parsed, here's the function to retrieve the list of dialogs.

function getDialogList() {  
var dialogname = dataObj["dialogname"];  
var entitytypename = dataObj["entitytypename"];   
  
//only execute query if the record already exists  
if((recordId != null) && (recordId != '')) {  
//set the path to the odata rest service  
//this can be hardcoded using the Organization Rest Service URL under Customizations->Developer Resources  
var odataPath = "https://" + window.location.host + "/XRMServices/2011/OrganizationData.svc";  
  
//no point inif entitytypename is provided  
if((entitytypename != null) && (entitytypename != ''))  
{  
//set up the odata query  
var retrieveReq = new XMLHttpRequest();  
var odataQuery = odataPath + "/WorkflowSet?$filter=Type/Value eq 1 and Category/Value eq 1 and StatusCode/Value eq 2";  
odataQuery += " and PrimaryEntity eq '"+entitytypename+"'";  
odataQuery += " and startswith(Name, '"+dialogname+"')";  
odataQuery += "&$select=Name, WorkflowId, Description, Category, Type, PrimaryEntity&$orderby=Name";  
retrieveReq.open("GET", odataQuery, false);  
  
//make sure we get json back  
retrieveReq.setRequestHeader("Accept", "application/json");  
retrieveReq.setRequestHeader("Content-Type", "application/json; charset=utf-8");  
  
//request/response will execute asynchronously, so we need a callback function  
retrieveReq.onreadystatechange = function () { generateLauncherMenu(this); };  
  
//send the request  
retrieveReq.send();  
}  
}  
}

Generating the launcher menu

Once the dialogs have been retrieved by the OData query, we can use jQuery to easily generate the contents of the launcher menu. There's lots of ways you can represent the dialogs to launch - buttons, hyperlinks, drop-down menu entries, etc. - but I think buttons are the best UI for a small set of dialogs.

The following function writes dialog-launching buttons to a div called launchDiv.

function generateLauncherMenu(retrieveReq) {  
//4 means a complete response  
if (retrieveReq.readyState == 4) {   
//parse response to json  
var retrieved = this.parent.JSON.parse(retrieveReq.responseText).d;   
  
for (var i = 0; i < retrieved.results.length; i++) {  
//create a button and append it to a launchdiv div  
var paramString = "\"" + retrieved.results[i].WorkflowId + "\",\"" + retrieved.results[i].PrimaryEntity+ "\",\"" +recordId + "\"";  
$('#launchDiv').append( "<button onclick='openDialog("+paramString+")'>Launch "+retrieved.results[i].Name+"</button><br />" );  
}  
};  
}

And then this function opens the dialog window:

function openDialog(processId, primaryEntity, entityId) {  
var url = "/cs/dialog/rundialog.aspx?DialogId=" + processId + "&EntityName=" + primaryEntity + "&ObjectId=" + entityId.replace('{','').replace('}','');  
window.open(parent.Xrm.Page.context.getClientUrl() + url);  
}

Putting it all together

Here's the complete web page you can upload as a web resource to your CRM system: dialog_launcher.htm (3.91 kb)

All you have to do is embed the web resource on your entity form and set the properties and custom parameter data like in the following image:

I have two account dialogs in my CRM system, Demo-Dialog #1 and Demo-Dialog #2. This is what the launcher menu looks like in a pre-Polaris, read-only form:

Here is what it looks like in a classic-mode form:

As always, happy coding!

A few weeks back, I wrote a post that showed how to retrieve and display FetchXML using jQuery in a Dynamics CRM web resource. In that example, I used jQuery's each() method to iterate through each result and append them to an HTML element on the page. Using each() is a good approach if you need to actually do something with each row, but if you just want to display data, XSLT is a much easier way to do it. In this post, I will show how to use client-side XSLT to format a FetchXML response for display.

Fetching the data

To display data, obviously we need to retrieve some data first. Using the approach from my earlier post, we will use the following FetchXML query to return a list of contacts:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">  
<entity name="contact">  
<attribute name="fullname" />  
<attribute name="contactid" />  
<attribute name="ownerid" />  
<attribute name="gendercode" />  
<attribute name="address1_stateorprovince" />  
<attribute name="createdon" />  
</entity>  
</fetch>

When I execute this query, I get the output in the attached contacts.xml file: contacts_raw.xml (50.68 kb).

Designing the display format

It we are going to use XSLT to format XML data, we will need an XSL style sheet. A detailed discussion of XSL is beyond the scope of this post, but I will point out a couple of things about my style sheet:

First, I use a template to match the root node, and then I loop through each entity with this for-each element:

<xsl:for-each select="//a:Entities/a:Entity">

The "//a:Entities" tells it to match an a:Entities element with any ancestors so you don't have to match all the way to the top-level s:Envelope ancestor.

Second, FetchXML represents an attribute (or formattedvalue) as an element with one "key" child and one "value" child. The "key" child is the attribute name, and the "value" is, obviously, the value. Thus, from each entity, I select the attribute and formattedvalue data to display using elements like this:

<xsl:value-of select="a:Attributes/a:KeyValuePairOfstringanyType[b:key='fullname']/b:value" />

What that means is "select the 'b:value' element that has an 'a:KeyValuePairOfstringanyType' that has a 'b:key' element equal to 'fullname.'" Alternatively, you can think abou it as "select the 'b:value' element that has a sibling 'b:key' element equal to 'fullname.'"

Here is the complete XSL style sheet: contacts.xsl (1.35 kb).

Once you have your XSL style sheet ready, upload it to your CRM instance as a web resource of type "Style Sheet (XSL)." Make sure you remember the relative path of the new resource. In my case it is "new_contacts.xsl."

Applying the transformation

Now that we have some FetchXML results and an XSL style sheet, all that's left to do is to transform the returned FetchXML and display it on the page. To do that, I have made a few modifications to my earlier FetchXML retrieval logic. Because jQuery retrieves things by default asynchronously, I start with retrieving the XSL document, then I use a callback function to retrieve the FetchXML results and finally I use a callback function to do the transformation. In the interest of not cluttering my code with global variables, I pass everything I need through the chain of callbacks. For example, my XSL retrieval method takes the FetchXML request as a parameter just so it can pass it to its callback. Then that callback takes the XSL document so that its callback has the XSL.

The code to do the apply and display the transformation is below:

function processData(xml, xsl) {  
/// <summary>  
/// transforms xml with a supplied xsl style sheet and appends the output to a div on the page  
/// </summary>  
  
//instantiate a variable to hold the transformed xml  
var resultDocument = "";  
resultDocument = TransformToHtmlText(xml, xsl);  
alert("Output started");  
$("#outputdiv").append(resultDocument);  
  
alert("Output complete");  
}  
  
function TransformToHtmlText(xmlDoc, xsltDoc) {  
/// <summary>  
/// Transforms a XML document to a HTML string by using a XSLT document  
/// 1. Use type XSLTProcessor, if browser (FF, Safari, Chrome etc) supports it  
/// 2. Use function [transformNode] on the XmlDocument, if browser (IE6, IE7, IE8) supports it  
/// 3. Use function transform on the XsltProcessor used for IE9 (which doesn't support [transformNode] any more)   
/// 4. Throws an error, when both types are not supported  
/// taken from "How to fix: DOMParser.TransformNode not supported in IE9"  
/// http://www.roelvanlisdonk.nl/?p=2113  
/// </summary>  
// 1.  
if (typeof (XSLTProcessor) != "undefined") {  
var xsltProcessor = new XSLTProcessor();  
xsltProcessor.importStylesheet(xsltDoc);  
var xmlFragment = xsltProcessor.transformToFragment(xmlDoc, document);  
return xmlFragment;  
}  
// 2.  
if (typeof (xmlDoc.transformNode) != "undefined") {  
return xmlDoc.transformNode(xsltDoc);  
}  
else {  
  
try {  
// 3  
if (window.ActiveXObject) {  
var xslt = new ActiveXObject("Msxml2.XSLTemplate");  
var xslDoc = new ActiveXObject("Msxml2.FreeThreadedDOMDocument");  
xslDoc.loadXML(xsltDoc.xml);  
xslt.style sheet = xslDoc;  
var xslProc = xslt.createProcessor();  
xslProc.input = xmlDoc;  
xslProc.transform();  
  
return xslProc.output;  
}  
}  
catch (e) {  
// 4  
alert("The type [XSLTProcessor] and the function [XmlDocument.transformNode] are not supported by this browser, can't transform XML document to HTML string!");  
return null;  
}  
  
}  
}

Two points to note:

1. The processData method doesn't apply the transformation itself. It passes the XML and XSL to another method called TransformToHtmlText.
2. The TransformToHtmlText method allows us to easily handle differences in the way that different browsers (or even different versions of the same browser) handle XML. For more information take a look at http://www.roelvanlisdonk.nl/?p=2113 and http://stackoverflow.com/questions/12149410/object-doesnt-support-property-or-method-transformnode-in-internet-explorer-1.

Trying it out

Here is a complete web page with the code for you to download and try out in your own CRM instance: fetchxml_xslt.htm (7.02 kb). Just upload it as you would any other web resource, publish and open. At that point you will be presented with a screen that looks like this:

Either leave the pre-populated FetchXML in the textarea or supply your own. Once you click the "fetch and process" button, you will start to see some output and a couple of status alert windows before the output finally displays. The end result should look something like this:

In part I of this series, I showed how to query Microsoft Dynamics CRM for aggregate data using FetchXML and then pass the results to Flot to generate a line chart. In this second part, I will expand on that to show how to query for and chart multi-series data. For my example today, I will be creating a chart that shows the number of contacts created by date and state (address, not statecode). In my CRM instance, I have created a set of contacts using characters from Cohen brothers movies. (Please, hold your "Big Lebowski" quotes.)

Changes to the previous approach

There are three main things we have to do differently here than before:

1. Update the FetchXML query to include an additional group-by field
2. Update the results parser to handle the additional group-by field
3. Send Flot an array of data series instead of just one

In addition to that, I have improved the original code in two more ways:

1. Fill in missing date values with 0 so the shape of the line better reflects reality
2. Shade weekend days in gray on the chart grid to help break up the weeks and highlight areas in which we might logically expect no activity to occur

Updated FetchXML

Changing the FetchXML query is easy. We just have to add the address1_stateorprovince field:

<fetch distinct='false' mapping='logical' aggregate='true'>  
<entity name='contact'>  
<attribute name='fullname' alias='plotValue' aggregate='count' />  
<attribute name='address1_stateorprovince' groupby='true' alias='seriesLabel' />  
<attribute name='createdon' groupby='true' dategrouping='day' alias='day' />  
<attribute name='createdon' groupby='true' dategrouping='month' alias='month' />  
<attribute name='createdon' groupby='true' dategrouping='year' alias='year' />  
</entity>  
</fetch>

The field we will use to create the data series is aliased as "seriesLabel" so we can have as generic a results parsing method as possible.

Updated results parser

The manner in which I wrote the getKeyValPairs function in part I won't return the seriesLabel field as part of the return object because only the numeric values come back in the a:FormattedValues element. The seriesLabel field is available via the a:Attributes element, so this required a minor change to my XML find logic. If you have downloaded my previous charting sample, you should be able to update the getKeyValPairs function in it with the newer version here without any issues.

function getKeyValPairs(entity, valuefield) {  
/// <summary>  
/// extracts an aggregate value and grouping components from a FetchXML response entity element and returns them in an array  
/// </summary>  
var outputArray = new Array();  
  
//first, get the aggregate value using its alias ("plotValue" in the original example)  
var pointValue = parseFloat(entity.find("a\\:KeyValuePairOfstringstring").filter(function() {  
return $(this).find('b\\:key').text() == valuefield;  
}).find("b\\:value").text().replace(/,/g,''));  
var groupingArray = [];  
  
//second, get the values for the group-by fields  
//unlike previous example, this uses the a:KeyValuePairOfstringanyType element   
//so we go down an extra "value" level when populating the value variable  
entity.find("a\\:KeyValuePairOfstringanyType").each(function() {  
return $(this).find('b\\:key').text() != valuefield;  
}).each(function() {  
var xmlElement = $(this);  
var key = xmlElement.find("b\\:key").text();  
var value = xmlElement.find("b\\:value").find("a\\:Value").text();  
var groupingText = '"' + key + '":"' + value + '"';  
groupingArray.push(groupingText);  
});  
var stringToEval = groupingArray.join();  
var dateFields = eval("({"+stringToEval+"})");  
outputArray.push(pointValue);  
outputArray.push(dateFields);  
  
return outputArray;  
}

Sending Flot the data

Flot expects data to be passed as an array of objects like this:

{   
label: "Series Label",   
data: [[x1,y1],[x2,y2]...   
}

In our example, we would have a separate object for each state's data. To build the array of data objects, we do the following:

1. Get a list of unique series labels (states)
2. Create a holding array for each series' data points
3. Assign each data point to its correct series in the holding array
4. Build the data array Flot expects from the holding array

As part of building the data array for Flot, we also do some date manipulation to fill in missing dates with 0 values.

Here are the functions that do all of this:

function processData(xml) {  
/// <summary>  
/// charts data returned in a FetchXML response using Flot  
/// </summary>  
  
alert("Charting started");  
  
//array to hold series labels  
var seriesList = [];  
  
//array to hold the series data before we get it ready for flot  
var pointSeries = [];  
  
//array to hold data to pass to flot  
var dataSeries = [];  
  
//array to hold all each unique date in our data set   
//this is so we can show dates with no data between the start and finish dates as 0  
var dateArray = [];  
  
//loop through xml and extract data from each "entity" element  
$("#chartdiv").append("Starting XML parsing . . . <br />");  
$(xml).find("a\\:Entity").each(function() {  
//pass the element to a function that extracts the aggregate value and the "group by" values  
//"plotValue" is the FetchXML alias for the data we want to display  
var pointData = getKeyValPairs($(this),"plotValue");   
  
//for each unique series label value  
if(seriesList.indexOf(pointData[1].seriesLabel) == -1) {  
//add label to array of labels  
seriesList.push(pointData[1].seriesLabel);  
  
//instantiate a new array to hold data for this series  
pointSeries.push(new Array());  
}  
  
//Flot requires datetimes to be passed as timestamp values  
var pointDate = new Date(pointData[1].year, pointData[1].month-1, pointData[1].day);  
var timestamp = pointDate.getTime();  
  
//for each unique date value  
if(dateArray.indexOf(timestamp) == -1) {  
//add date timestamp to array of dates  
dateArray.push(timestamp);  
}  
  
//get the index of the series to which this particular value corresponds  
var seriesNumber = seriesList.indexOf(pointData[1].seriesLabel);  
  
//add this particular data value to the correct array in the point series array  
pointSeries[seriesNumber].push([timestamp, pointData[0]]);  
  
});  
  
//sort the unique dates so we can find the min and max dates later  
dateArray.sort(function(a,b){return a-b;});  
  
//we have a function that fills in missing dates with 0 values  
//so to make sure all series have the same set of data, we will add a dummy date to both ends of the range with 0 values  
//after we have "filled out" any missing values in the series, we will remove the dummy dates  
//get milliseconds per day so we can just add/subtract the value of the "day" variable later  
var minute = 1000 * 60;  
var hour = minute * 60;  
var day = hour * 24;  
  
//for each series  
for(var i=0; i<seriesList.length; i++) {  
//the label we pass to Flot is the value from the series label array  
var seriesLabel = seriesList[i];  
  
//get a time series data variable from the correct array in the pointseries aray  
var tsData = pointSeries[i];  
  
//add a day with a 0 value to the beginning of the series  
tsData.push([dateArray[0]-day,0]);  
  
//add a day with a 0 value to the end of the series  
tsData.push([dateArray[dateArray.length-1]+day,0]);  
  
//sort the data points by date  
tsData.sort(function(a,b){return a[0]-b[0];});  
  
//fill in missing dates with 0-value points  
tsData = newDataArray(tsData);  
  
//remove the first "dummy" date  
tsData.shift();  
  
//remove the last "dummy" date  
tsData.pop();  
  
//create a new object to represent this series  
var seriesObj = new Object();  
  
//set a label property  
seriesObj.label = seriesLabel;  
  
//set a data property  
seriesObj.data = tsData;  
  
//add this object to the dataseries array that will be passed to Flot  
dataSeries.push(seriesObj);  
}  
  
//plot the data with some display options  
//see examples and API documentation at http://www.flotcharts.org/  
$.plot($("#chartdiv"),   
dataSeries, {   
series: {  
lines: { show: true },  
points: { show: true }  
},   
grid: {   
hoverable: true ,  
markings: weekendAreas //calls the weekendAreas function to shade weekend days in gray  
},  
xaxis: {   
tickLength: 5,  
mode: "time",  
timeformat: "%m/%d/%y"  
},  
yaxis: {  
tickDecimals: 0,  
min: 0  
}  
}  
);  
  
alert("Charting complete");  
}  
  
//this function is used to fill in the missing date values with 0 values  
//taken from http://stackoverflow.com/questions/14522667/how-to-configure-flot-to-draw-missing-time-series-on-y-axis-at-point-zero  
function newDataArray(data) {  
var startDay = data[0][0],  
newData = [data[0]];  
  
for (i = 1; i < data.length; i++) {  
var diff = dateDiff(data[i - 1][0], data[i][0]);  
var startDate = new Date(data[i - 1][0]);  
if (diff > 1) {  
for (j = 0; j < diff - 1; j++) {  
var fillDate = new Date(startDate).setDate(startDate.getDate() + (j + 1));  
newData.push([fillDate, 0]);  
}  
}  
newData.push(data[i]);  
}  
return newData;  
}  
  
  
/* helper function to find date differences*/  
function dateDiff(d1, d2) {  
return Math.floor((d2 - d1) / (1000 * 60 * 60 * 24));  
}

Format for the weekends

The final difference from part I is calling a custom function to format the weekends on the chart. This is done in this part of the processData function above:

grid: {   
hoverable: true ,  
markings: weekendAreas //calls the weekendAreas function to shade weekend days in gray  
},

The weekendAreas function:

//helper for returning the weekends in a period  
//taken from http://www.flotcharts.org/flot/examples/visitors/index.html  
function weekendAreas(axes) {  
var markings = [];  
var d = new Date(axes.xaxis.min);  
// go to the first Saturday  
d.setUTCDate(d.getUTCDate() - ((d.getUTCDay() + 1) % 7))  
d.setUTCSeconds(0);  
d.setUTCMinutes(0);  
d.setUTCHours(0);  
var i = d.getTime();  
do {  
// when we don't set yaxis, the rectangle automatically  
// extends to infinity upwards and downwards  
markings.push({ xaxis: { from: i, to: i + 2 * 24 * 60 * 60 * 1000 } });  
i += 7 * 24 * 60 * 60 * 1000;  
} while (i < axes.xaxis.max);  
  
return markings;  
}

Trying it out

Here is a complete web page with the code for you to download and try out in your own CRM instance chart_example2.htm (12.34 kb). Just upload it as you would any other web resource, publish and open.

When I run it in my CRM instance, this chart is generated.

As you can see, the chart gets a little busy, and it's hard to differentiate between the different series where they overlap, but there's a lot more you can do with Flot formatting options to make it look better. Also, I haven't covered everything Flot can do, so I encourage you to look at the Flot examples page to see some other possibilities like bar charts, stacked charts, panning/zooming, etc.

Earlier this week I posted an entry about using FetchXML and JQuery in a Dynamics CRM 2011 web resource. The reason I first started looking at those two together was that I wanted to see if I could generate better looking line charts than are available out of the box (spoiler alert: I did). In this post I will show how to combine FetchXML and JQuery with the Flot plotting library to produce line charts from CRM data aggregations.

In my career, one of the data visualizations I have encountered the most is to plot some value over unit time. For example, we can try to measure an agent's productivity by counting the number of phone calls per day. Because of the way Dynamics CRM stores data, if we restrict our queries to its database instead of a data warehouse, generally we can report on actions taken per unit time, but we can't report on how things change over time. We can say Agent X created 100 phone call records last week, but we can't tell how many overdue phone call records Agent X had in the queue yesterday. In my example here, I will show how to plot number of contacts created per day.

Top of the page stuff

Charting with Flot requires jQuery, Flot (obviously) and Excanvas in Internet Explorer. Here is how I pull those libraries in from CDNs, but if your CRM instance is accessed over a LAN, you should host them locally.

<!--[if lte IE 8]><script language="javascript" type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/flot/0.7/excanvas.min.js"></script><![endif]-->  
<script src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.9.0.min.js"></script>  
<script src="https://cdnjs.cloudflare.com/ajax/libs/flot/0.7/jquery.flot.min.js"></script>

Also, Flot doesn't work so well in Internet Explorer quirks mode, so make sure you set the page to display in standards mode by declaring an HTML doctype like so in the first line:

Writing the query

As I explained previously, if you want to query aggregate data from inside JavaScript, you have to use FetchXML. While researching this topic, the examples I found weren't necessarily the clearest, but MSDN has a good overview here.

Once you get the hang of the FetchXML syntax, if you can write your query in SQL group-by syntax, it shouldn't be too difficult to translate it to FetchXML. Here is the query we will use to count contacts by their createdon date:

<fetch distinct='false' mapping='logical' aggregate='true'>  
<entity name='contact'>  
<attribute name='fullname' alias='plotValue' aggregate='count' />  
<attribute name='createdon' groupby='true' dategrouping='day' alias='day' />  
<attribute name='createdon' groupby='true' dategrouping='month' alias='month' />  
<attribute name='createdon' groupby='true' dategrouping='year' alias='year' />  
</entity>  
</fetch>

We have a single data field aliased as plotValue that is equivalent to SQL's count(fullname) as plotValue, and then there are three groupby fields that are based on the createdon field. They have each been given an alias that corresponds to the specific datepart.

Executing the query

To execute the FetchXML and retrieve the results, we will use the same basic technique I showed in my previous post.

1. Get FetchXML query from a textarea field.
2. Build a SOAP message.
3. Send the SOAP message to CRM and register a callback function to process the data.

To do this, we will use the same function executeFetchCommand, buildFetchRequest and sendQuery methods from my previous post. We will be using a different processData method, though the name will stay the same.

Charting the data

Once the data is returned by CRM, we can generate the chart. To do that, we will do the following:

2. Loop through XML to extract count for each date.
3. Convert the count and date values to an array of X and Y values for Flot to plot.
4. Call the Flot plotting method.
5. Bind an event to our chart to display tooltip messages that show the X and Y values when the user mouses over a specific point.

First, our new processData callback function looks like this:

function processData(xml) {  
/// <summary>  
/// charts data returned in a FetchXML response using Flot  
/// </summary>  
  
alert("Charting started");  
  
//create a label for the chart points  
var yLabel = "Contacts created";  
  
//create an array to hold the plot data  
var series1 = [];  
  
//loop through xml and extract data from each "entity" element  
$("#chartdiv").append("Starting XML parsing . . . <br />");  
$(xml).find("a\\:Entity").each(function() {  
//pass the element to a function that extracts the aggregate value and the "group by" values  
//"plotValue" is the FetchXML alias for the data we want to display  
var pointData = getKeyValPairs($(this),"plotValue");   
  
//Flot requires datetimes to be passed as timestamp values  
var dateString = (pointData[1].year + "-" + pointData[1].month + "-" + pointData[1].day).replace(/,/g,'');  
var timestamp = new Date(pointData[1].year.replace(/,/g,''), parseInt(pointData[1].month)-1, pointData[1].day).getTime();  
  
//add x (timestamp) and y (aggregate data value) values to the data series array  
series1.push([timestamp, pointData[0]]);  
});  
  
//sort series array by date - Flot plots in order, so line can look wrong if we don't do this  
series1.sort(function(a,b){return a[0]-b[0];});  
  
//plot the data with some display options  
//see examples and API documentation at http://www.flotcharts.org/  
$.plot($("#chartdiv"),   
[ { data: series1, label: yLabel} ], {   
series: {  
lines: { show: true },  
points: { show: true }  
},   
grid: { hoverable: true },  
xaxis: {   
tickSize: [1, "day"],  
mode: "time",  
timeformat: "%m/%d/%y"  
}  
}  
);  
  
alert("Charting complete");  
}

In this method first we loop through the data and pass each element to a getKeyValPairs helper function to extract the necessary fields. It returns an array with the Y value to plot as element 0 and a JSON object containing all other fields as element 1. It looks like this:

function getKeyValPairs(entity, valuefield) {  
/// <summary>  
/// extracts an aggregate value and grouping components from a FetchXML response entity element and returns them in an array  
/// </summary>  
var outputArray = new Array();  
  
//first get the aggregate value using its alias ("plotValue" in the original example)  
var pointValue = parseFloat(entity.find("a\\:KeyValuePairOfstringstring").filter(function() {  
return $(this).find('b\\:key').text() == valuefield;  
}).find("b\\:value").text().replace(/,/g,''));  
var groupingArray = [];  
entity.find("a\\:KeyValuePairOfstringstring").filter(function() {  
return $(this).find('b\\:key').text() != valuefield;  
}).each(function() {  
var xmlElement = $(this);  
var key = xmlElement.find("b\\:key").text();  
var value = xmlElement.find("b\\:value").text();  
var groupingText = '"' + key + '":"' + value + '"';  
groupingArray.push(groupingText);  
});  
var stringToEval = groupingArray.join();  
var dateFields = eval("({"+stringToEval+"})");  
outputArray.push(pointValue);  
outputArray.push(dateFields);  
  
return outputArray;  
}

Once the data fields are returned by they getKeyValPairs method, the processData function creates a timestamp from the supplied month, day and year fields (Flot requires timestamps to plot by datetime), and then adds the timestamp and count to an array.

After the loop is complete, we sort the array by date to make sure the line will be plotted along our points in the correct order. Finally we call the Flot plot function with the data array and some pre-determined options. There are a lot of cool configuration options you can specify for Flot plots, but explaining them is beyond the scope of this point. Please take a look at the Flot examples page for more.

Finally we need some code to do the tooltip display. First we have a generic showTooltip method that will be executed when the mouse moves over a data point. You can customize the style to your liking.

function showTooltip(x, y, contents) {  
/// <summary>  
/// function to show tooltip when user mouses over data points  
/// </summary>  
$('<div id="tooltip">' + contents + '</div>').css( {  
position: 'absolute',  
display: 'none',  
top: y + 5,  
left: x + 5,  
border: '1px solid #fdd',  
padding: '2px',  
'background-color': '#fee',  
opacity: 0.80  
}).appendTo("body").fadeIn(200);  
}

Then we have code that binds a function to the chart plothover event so that the showTooltip method gets executed at the right time with the right data:

//bind a function to execute the showTooltip function at the appropriate time  
var previousPoint = null;  
$("#chartdiv").bind("plothover", function (event, pos, item) {  
$("#x").text(pos.x.toFixed(2));  
$("#y").text(pos.y.toFixed(2));  
  
if (item) {  
if (previousPoint != item.dataIndex) {  
previousPoint = item.dataIndex;  
  
$("#tooltip").remove();  
var date = new Date(item.datapoint[0]);  
var x = item.datapoint[0].toFixed(2),  
y = item.datapoint[1];  
  
//call showTooltip with the timestamp converted back to mm/dd/yyyy format  
showTooltip(item.pageX, item.pageY,  
item.series.label + " on " + (parseInt(date.getMonth()+1)) + "/" + date.getDate() + "/" + date.getFullYear() + " = " + y);  
}  
}  
else {  
$("#tooltip").remove();  
previousPoint = null;   
}  
  
}); 

Trying it out

Here is a complete web page with the code for you to download and try out in your own CRM instance chart_example.htm (8.30 kb). Just upload it as you would any other web resource, publish and open. At that point you will be presented with a screen that looks like this:

Either leave the pre-populated FetchXML in the textarea or supply your own that does a count by month, day and year. As long as it returns those three group-by fields and a Y value aliased as "plotValue," it should work. Once you click the "fetch and process" button, you will start to see some output and a couple of status alert windows before the output finally displays. The end result should look like this:

Of course this chart is actually not that exciting, but once you take a closer look at how you can format the line chart output with Flot, you'll see there are all sorts of interesting things you can do. You can also embed the charts in dashboards as a replacement for the existing Dynamics CRM line chart controls. Stay tuned for my next post when I show how to generate multi-series charts.

Over the weekend I started looking at a hobby project that involved querying and working with aggregate data from Dynamics CRM 2011 inside a hosted web resource using jQuery. Initially had I planned to use the OData/REST endpoint since that is much sexier than SOAP lately, but after a quick web search I realized that OData doesn't support "group by" queries, so that left me looking at FetchXML. As I mentioned in a post last month, I've generally avoided working with FetchXML, so I wasn't even sure where to get started to use it inside JavaScript. After another quick web search, I came across a great post on the Customer Effective blog called Execute Fetch from JavaScript in CRM 2011. That post shows a custom FetchUtil.js library that does three things:

1. Create a properly formatted SOAP request for a FetchXML query
2. Execute the request using the XMLHttpRequest object
3. Return the XML response to the browser

Using this original post to point me in the right direction, I combined the logic for creating the SOAP request with new jQuery code to handle the request and response. Here's how it works:

Getting the FetchXML

First we need to get the FetchXML to execute. For the purpose of this example, suppose we allow a user to execute ad-hoc FetchXML queries, so we need a method to read FetchXML from a textarea on a web page and then start processing. This method will be executed when a button is pushed:

function executeFetchCommand() {   
/// <summary>  
/// reads user-supplied FetchXML contained in the txtFetch textarea and starts processing  
/// </summary>  
  
//generate the SOAP envelope  
var fetchRequest = buildFetchRequest($("textarea#txtFetch").val());  
  
//send the request to CRM  
sendQuery(fetchRequest);  
}

The buildFetchRequest and sendQuery methods are described in the following sections.

Generating the request

First we need to generate the SOAP envelope from a FetchXML query. This method takes the query as an input parameter, properly encodes it and wraps it inside the envelope.

function buildFetchRequest(fetch) {  
/// <summary>  
/// builds a properly formatted FetchXML request  
/// based on Paul Way's blog post "Execute Fetch from JavaScript in CRM 2011"  
/// http://blog.customereffective.com/blog/2011/05/execute-fetch-from-javascript-in-crm-2011.html  
/// </summary>  
var request = "<s:Envelope xmlns:s=\"http://schemas.xmlsoap.org/soap/envelope/\">";  
request += "<s:Body>";   
  
request += '<Execute xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services">' +   
'<request i:type="b:RetrieveMultipleRequest" ' +   
' xmlns:b="http://schemas.microsoft.com/xrm/2011/Contracts" ' +   
' xmlns:i="http://www.w3.org/2001/XMLSchema-instance">' +   
'<b:Parameters xmlns:c="http://schemas.datacontract.org/2004/07/System.Collections.Generic">' +   
'<b:KeyValuePairOfstringanyType>' +   
'<c:key>Query</c:key>' +   
'<c:value i:type="b:FetchExpression">' +   
'<b:Query>';  
  
request += CrmEncodeDecode.CrmXmlEncode(fetch);   
  
request += '</b:Query>' +   
'</c:value>' +   
'</b:KeyValuePairOfstringanyType>' +   
'</b:Parameters>' +   
'<b:RequestId i:nil="true"/>' +   
'<b:RequestName>RetrieveMultiple</b:RequestName>' +   
'</request>' +   
'</Execute>';   
  
request += '</s:Body></s:Envelope>';   
return request;  
}

Executing the query

Once you have the SOAP envelope ready, you need to send the request to your CRM server. This method takes the SOAP envelope generated in the previous step, makes an asynchronous call to the CRM 2011 organization service using the jQuery ajax method and registers a callback function to process the returned results. (It also writes some status info to an output region of a web page to make it easier for you to follow the code execution.)

function sendQuery(fetchRequest) {  
/// <summary>  
/// uses jQuery ajax method to executes a FetchXML query and register a callback function  
/// </summary>  
  
//path to CRM root  
var server = window.location.protocol + "//" + window.location.host;  
  
//full path to CRM organization service - you may need to modify this depending on your particular situation  
var path = server + "/XRMServices/2011/Organization.svc/web";  
  
$("#outputdiv").append("Starting ajax request . . . <br />");  
$.ajax({  
type: "POST",  
dataType: "xml",  
contentType: "text/xml; charset=utf-8",  
processData: false,  
url: path,  
data: fetchRequest,  
beforeSend: function( xhr ){  
xhr.setRequestHeader( //without the SOAPAction header, CRM will return a 500 error  
"SOAPAction",  
"http://schemas.microsoft.com/xrm/2011/ Contracts/Services/IOrganizationService/Execute" //remove the extra space inserted to help this line wrap  
);   
}  
}).done(function(data) {  
//if successful, pass returned xml and the plot label to the charting function  
processData(data);  
}).fail(function(jqXHR, textStatus, errorThrown ) {  
//if unsuccessful, generate an error alert message  
alert( "Request failed: " + textStatus + "\n" + errorThrown );  
});  
$("#outputdiv").append("Ajax request complete. Output will be processed asynchronously. <br />");  
}

Here are a few things to note:

1. The inside the "done" function is where we tell jQuery what to do when a successful response returns data. We will look at the processData method more closely in the next section.
2. The "fail" function generates an alert window with error output. You could do something more sophisticated.
3. You probably want to leave all the other ajax configuration options alone unless you have a good reason not to. It took a good bit of trial and error for me to get this right (probably because I was up too late at night working it out, but I digress . . . ).

Handling the response

Assuming that everything went according to plan, our callback function will be called with the query results. At this point, you can do anything with them that you would normally do with XML inside of JavaScript. You can also convert the XML to JSON if that's more your speed. For demonstration purposes, I will show how to loop through the results and display the key-value pairs for each field returned.

Before I show the code to process the results, let's take a look at what the results would look like for this theoretical query:

<fetch distinct='false' mapping='logical' aggregate='true'>  
<entity name='contact'>  
<attribute name='fullname' alias='contact_count' aggregate='count' />  
<attribute name='createdon' groupby='true' dategrouping='day' alias='day' />  
<attribute name='createdon' groupby='true' dategrouping='month' alias='month' />  
<attribute name='createdon' groupby='true' dategrouping='year' alias='year' />  
</entity>  
</fetch>

In this query I am counting the number of contacts created by day, month and year. When I execute it against CRM, I get output that looks like the contents of the attached file: response.xml (6.76 kb) Here are some important points:

1. Each row of data is represented as an "a:Entity" element.
2. Individual attribute names and values can be found in the an entity's "a:Attributes" element and also in its "a:FormattedValues" element. In this example I will be working with the "a:FormattedValues" element because it's marginally easier to parse, however you may find times that it makes more sense to work with "a:Attributes" instead.
3. Everything under "a:FormattedValues" is represented as a string, so we would need to convert to other datatypes where necessary (ie. building a date object from the returned month / day / year values).
4. Each key-value pair under "a:FormattedValues" is contained in an "a:KeyValuePairOfstringstring" element.

Now that you know what the response from CRM looks like, here is the method that parses it and writes the output:

function processData(xml) {  
/// <summary>  
/// writes key-value pairs to a div  
/// </summary>  
  
alert("Output started");  
$("#outputdiv").append("OUTPUT FROM CRM <br />");  
  
//outer loop  
$(xml).find("a\\:Entity").each(function() {  
//write entity  
$("#outputdiv").append("Entity <br />");  
//inner loop  
$(this).find("a\\:KeyValuePairOfstringstring").each(function() {  
var xmlElement = $(this);  
var key = xmlElement.find("b\\:key").text();  
var value = xmlElement.find("b\\:value").text();  
$("#outputdiv").append("  Key: " + key + " Value: " + value + "<br />");  
  
});  
});  
  
alert("Output complete");  
}

You'll see there are two loops that use the jQuery find method. First the outer loop finds each a:Entity element, and then it passes that element to another function that executes the inner loop to find each a:KeyValuePairOfstringstring element. Then that element is passed to another function that extracts the text of the "b:key" and and "b:value" elements and writes them to the web page output div using jQuery's append method.

Trying it out

Here is a complete web page with the code for you to download and try out in your own CRM instance: jquery_fetchxml.htm (4.53 kb). Just upload it as you would any other web resource, publish and open. At that point you will be presented with a screen that looks like this:

Either leave the pre-populated FetchXML in the textarea or supply your own. Once you click the "fetch and process" button, you will start to see some output and a couple of status alert windows before the output finally displays. The end result should look like this:

As mentioned earlier, the attribute values retrieved are all strings, so you'll see the years have commas in them (2,013 instead of 2013). The solution is to either get the integer value from the XML response or parse the text value into an integer.

Caveats:

1. In the demo web page, I am using the jQuery library hosted by the Microsoft Ajax Content Delivery Network. If your users are accessing a CRM instance on your LAN, it would be better to host the jQuery library inside of CRM.
2. I don't know how this page hosted as a web resource will work in browsers other than Internet Explorer. Initially I did most of the jQuery prototyping work with a locally stored XML file on my PC using Chrome, and that code didn't work in IE without a lot of changes. Particularly noteworthy is that Chrome could find the XML elements without me needing to specify a namespace ("a:" or "b:"), but IE wouldn't. I did not go back and test whether the modified find selectors worked in Chrome.