CRM 2011 – Using OData and JSON

[tweetmeme source=”BenHosk” only_single=false]

I found a very interesting blog today called CRMScape.  It had a fantastic article on using OData and JSON.  I have used OData and JSON to retrieve CustomerAddress entity information.

You can do it if you use the JSON2.js script and modify the example code found in CRM 2011 SDK, which you can find here.

The walkthrough/tutorial below is excellent and will save you some of the frustrations I had.

The blog also has quite a few indepth development articles.  Go and check out this article and others which can be found on his blog

CRM 2011 OData, JSON and CRM Forms

1. Generating OData queries

OData commands can easily be tested out in IE. To help with this, you will want to turn off the feed reading view in IE.


The first thing that you will want is the name of the Sets that you will be calling on. If you use a path like the following it will provide a listing of the names of your sets.http://crmdev:5555/CRMDEV/XRMServices/2011/OrganizationData.svc

You will see a list of them in the following format.

- <collection href="AccountSet">

Since these names are case sensitive you will want to look at the names of your custom entities. Your stock items like AccountSet and ContactSet will be camel cased, but your custom entities will likely show up as new_myentitySet.


As you can see the field names use the Schema Name in CRM rather than the lowercase name the is used on the CRM forms. This is something that can cause confusion.

<d:Address1_Name m:null="true" />
<d:Address1_Telephone2 m:null="true" />
<d:OverriddenCreatedOn m:type="Edm.DateTime" m:null="true" />
<d:Telephone3 m:null="true" />
<d:DoNotBulkPostalMail m:type="Edm.Boolean">false</d:DoNotBulkPostalMail>
If you specify the guid of that entity, the results returned will be for that one entity. All fields are returned whether they are null or not. This listing will show you the exact case of each attribute name that you may want to query.
You can save bandwidth by only selecting the fields that you need and only those fields will be returned. Below will only return the AccountNumber and AccountName
There are numerous references from MS explaining how to build OData Queries, however, Rhett Clinton’s recent addition to Codeplex is probably the easiest way to generate these queries. His tool can be found at the link below.
Screen shot of his Query Designer
CRM 2011 OData Query Designer
2. Using OData Queries with JSON
To use any of the following in a javascript library as webresource in CRM 2011 solution, you will first need to include a jquery library and a json library. The jquery1.4.1.min.js and json2.js files can be found in the most recent MS CRM SDK.sdk\samplecode\js\restendpoint\jqueryrestdataoperations\jqueryrestdataoperations\scripts. Add these a libraries and include them above the JavaScript library that you put your code in. Click here to see what that looks like in the Forms Property page.
To utilize these OData queries in a CRM Form using JSON, there is a pretty standard template to use. Just set the odataSelect below to your OData select url, and use the appropriate return method.
var odataSelect = "Your OData Query";

       type: "GET",
       contentType: "application/json; charset=utf-8",
       datatype: "json",
       url: odataSelect,
       beforeSend: function (XMLHttpRequest) { XMLHttpRequest.setRequestHeader("Accept", "application/json"); },
       success: function (data, textStatus, XmlHttpRequest)
               // Use only one of these two methods

               // Use for a selection that may return multiple entities

               // Use for a single selected entity

       error: function (XmlHttpRequest, textStatus, errorThrown) { alert('OData Select Failed: ' + odataSelect); }
Notice the two methods:
  • ProcessReturnedEntities(data.d.results)
  • ProcessReturnedEntity(data.d)

When selecting what could be any number of entities, there will be an array returned, and you want to look at the data.d.results.  When selecting a specific Guid there is no results array created, and you will need to just look at the data.d that is returned.

For example:
function ProcessReturnedEntities(ManyEntities)
  for( i=0; i< ManyEntities.length; i++)
     var oneEntity = ManyEntities[i];
     var accountNumberAttribute = oneEntity.AccountNumber;    

     var accountNumberValue = eval(oneEntity.AccountNumber);

function ProcessReturnedEntity(OneEntity)
     var oneEntity = OneEntity;
     var accountNumber = oneEntity.AccountNumber;

     var accountNumberValue = eval(oneEntity.AccountNumber);
Entity Attributes
As you can see the JavaScript entity objects returned have attributes named with matching camel casing in the OData query.
In that case of simple CRM attributes like:
  • string
  • memo
  • decimal
  • double
  • integer

You can get the value from them by simply using eval like shown in the example above.

For the following CRM attributes there is more involved.

  • optionset
  • money
  • datetime
  • lookup

For example:

var moneyValue = eval( oneEntity.new_MoneyAttribute.Value);
var optionSetValue = eval ( oneEntity.new_OptionSet.Value);
Setting CRM Form Fields with Queried Values
This gets a bit more complex when setting values to CRM form controls.
  1. The form field names are all lower case, so the retrieved names do not match.
  2. The form fields have validation and maintain more types than the returned OData values have.
  3. There is some conversion required between them.

You can find out the type of a form control as follows:

var attrType = Xrm.Page.getAttribute("accountnumber").getAttributeType();

With the type you can then use the appropriate means to set form controls.

string, memo fields:

decimal, double fields:
integer fields
money fields
optionset fields
date fields
var fieldValue = eval(oneEntity.new_DateTime);
var dateValue = new Date(parseInt(fieldValue.replace("/Date(", "").replace(")/", ""), 10));

The addition of support for JSON, OData queries in MS CRM 2011 has

added some great power to what can be done easily in the JavaScript of a CRM form. This should reduce the number of solutions that require web applications running in iframes dramatically. This is a good thing since MS is phasing out the ISV folder for web applications running in the same app pool on a web server and there is currently no support for aspx files in CRM solutions

6 thoughts on “CRM 2011 – Using OData and JSON

  1. Ray April 5, 2011 / 3:40 pm

    Hi Hosk,

    I want to thank you first for this very detail walk through. I am very new to CRM.
    My question is I added Json2 and Jquery to web Resource.
    I also added them to Account.form properties.form libraries .
    But when I use var jsonEntity = window.JSON.stringify(CRMObject);
    I got an error : window.JSON is null or not an object
    I found an explaination from below link, but I don’t know what I can do in CRM 2011. I am using IE8:

    Thank you very much for any help.



  2. Hosk April 5, 2011 / 3:57 pm

    So from the sounds of it you have added the files to the Account library, which is one problem I had when I had problems added external javascript files, have you added the file like this

    You could try pasting the json2.js contents into your javascript file so you don’t need the javascript reference at the top of the file.

    I would then try using the internet explorer debug (you can bring it up by pressing F12) then try and step through the code by debugging the script. You should be able to step through the json2 javascript which might tell you more about the error


  3. ian November 14, 2011 / 11:32 pm

    have you got/ seen any examples using paging?


  4. wso reviews December 20, 2011 / 8:49 pm

    Fantastic beat ! I wish to apprentice while you amend your site, how can i subscribe for a weblog site? The account aided me a applicable deal. I had been a little bit familiar of this your broadcast offered brilliant clear idea


  5. dynamicscrmdenver April 7, 2012 / 2:56 am

    Nice, thank you. In particular, your differentiation of the multiple return (json.d.results) versus the single object (json.d) help me enormously. It also made me tell myself, duh!,


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s