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:
- Create a properly formatted SOAP request for a FetchXML query
- Execute the request using the XMLHttpRequest object
- 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:
- 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.
- The "fail" function generates an alert window with error output. You could do something more sophisticated.
- 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:
- Each row of data is represented as an "a:Entity" element.
- 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.
- 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).
- 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:
- 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.
- 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.