torsdag 23 augusti 2012

JSON Querys

Fetching data is pretty much what a CRM system is about. Being able to do advanced searches and queries using Javascript is indeed a nice feature, it has however given me some grey hairs.

Prior to Dynamics CRM 2011 you were forced to write the fetch with a lot of XML. With the introduction of CRM 2011 Microsoft introduced the OData protocol to make it easier to fetch the data you wanted from the system.

Even though this change is much welcome it is not without hassle I have entered the world of OData and JSON in CRM. Given the amount of blogs about CRM, the SDK and the Technet I found it quite hard to find how to make a query with the OData protocol making a function that returned data or an object. Most references used the OData protocol to just update a field on the form. The problem I was facing needed a function that just returned a value, like most functions do.

First thing's first. How do you set up the query?
Looking around on the internet gives a lot of answers to this question. I found this blog: by Deepak Kumar, which gave me a basic understanding how to set up the query

function OnLoad() {
    var serverUrl = "http://" + + "/" +
    var primarycontact = Xrm.Page.getAttribute("primarycontactid").getValue();
    var primarycontactid = primarycontact[0].id;

 // Creating the Odata Endpoint
    var oDataPath = serverUrl + "/XRMServices/2011/OrganizationData.svc";
    var retrieveReq = new XMLHttpRequest();
    var Odata = oDataPath + 
              "/ContactSet?$select=EMailAddress1&$filter=ContactId eq guid'" +
               primarycontactid + "'";"GET", Odata, false);
    retrieveReq.setRequestHeader("Accept", "application/json");
    retrieveReq.setRequestHeader("Content-Type", "application/json; charset=utf-8");
    retrieveReq.onreadystatechange = function () { retrieveReqCallBack(this); };

function retrieveReqCallBack(retrieveReq) {
    if (retrieveReq.readyState == 4 /* complete */) {
        var retrieved = this.parent.JSON.parse(retrieveReq.responseText).d;
        var EmailAddress = retrieved.results[0].EMailAddress1;

Looking att this code things started to make sense, The server URL is taken from the form's host and the context's organization. Further the oDataPath here is copied from the OData address from the developrs resources in CRM.

Deepak has chosen to have this example showing how to fetch the Email address of the primary contact. He has taken the primary contact ID from the form to use that as a keyword in the search, still pretty easy to understand.
Then he sets up the request, an XMLHttpRequest, and now things got a bit tricky for me since the similarities to this tutorial ended. The Odata variable declared after the XMLHttpRequest deserves some extra attention.

    var Odata = oDataPath + 
              "/ContactSet?$select=EMailAddress1&$filter=ContactId eq guid'" +
               primarycontactid + "'";

ContactSet is the declaration that we want to search for one or more contacts. So is there a list of what "sets" that are available? No, I haven't found one yet, if anyone has seen one, please let me know. The funny thing about these "sets" is that they are of course case sensitive. If you would enter "contactset" you would end up in a "500-error". I was searching for users so I started out thinking it would be UserSet since all querys I had found was written with a capital first letter and capital S in set.

As you might imagine, UserSet is wrong, so I tried SystemuserSet, which also was wrong. Considering I had quite the search string, it took some time for me to narrow down where the search went wrong and it started on square on. SystemUserSet was the correct term in this case, which probably is logical if you're used to the syntax which I unfortunately isn't.

After the entity we're searching for comes the goal of the search. If you leave out the $select part, you should get the entire set of fields, however if you want to narrow down your search stack the fields you're interested in separated by commas. You might notice that EMailAddress is written a bit odd. I would have guessed that Email is one word and would probably have gotten an error there as well, in my case I was looking for FullName and the SystemUserId.

Next is the $filter, which is the search criteria, what are you narrowing your search with. If you want one hit, a guid is the main choice since it will give just one result. However, if you don't know the guid of the record you have to use other fields. Notice that if you're searching by id, you need to mark that it's a guid. The different search patterns can be seen on and you can combine search patterns with "and" or "or".

Moving down the code I found the next part with:"GET", Odata, false);

This tells us that it's a fetch, it's Odata and the "false" is telling us that the query is synchronous. Had it been a "true" at the end, it would have been an asynchronous retrieve. After that there are two rows setting the header which I haven't dug into much, I simply accepted them. And then there's a function declaration.

This got my attention, but there were other examples of that declaration being there but according to W3School, this was perfectly normal so I accepted it. Last there is a send message ending that code. But there's still the function call that is made by the onreadystatechange where the result is processed and in this case sent out as an alert.

Back to my problem at hand. Putting all this in a function, and having another function hadling the results didn't help me since I wanted something returned from the first function. Since I am very new to the OData protocol I really didn't know how to solve this and most tutorials looked like this, or with another approach using some ajax call.

Finally I found /2011/12/13/read-records-synchronously-using-json-in-crm-2011.aspx.
The answer was surprisingly easy, just put all the code from the callback function after the send request and then you can return you data to the calling function. How come there are very few tutorials with this solution.

Now I had my query and I had my results. I just needed to see what I had gotten back from the system. How many records are there? Started looking for answers on the internet. Again it seemed like I was doing somthing that not a lot of people are insterested in. This time it really was as simple as "retrieved.results.length", which I acctually tried when I thought I had the query correct but alas since I had gotten one of the search words wrong, the result was not an array of length zero as one might imagine but not instansiated since I got a 404-error from the search.

The debugging is really a mess. I found it easiest to do the query debugging in a browser and just try how the cases of the keywords should look like

To sum this quite long post in a few bullets
  • Use your browser to test the query
  • Build the query part by part, testing all the way, it's very easy to get it wrong
  • You don't have to push the results into a fuction of it's own, I know it sounds silly but I got the impression you had to do it that way.
  • You can narrow down the results to just a few fields, which I would think improves performance
  • If you find a complete list of "sets" from a vanilla CRM, please let me know :)
Happy coding and I hope that this will get easier soon!

Rickard Norström
Developer at CRM-Konsulterna