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">
  <atom:title>AccountSet</atom:title>
  </collection>

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.

http://crmdev:5555/CRMDEV/XRMServices/2011/OrganizationData.svc/AccountSet

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.

http://crmdev:5555/CRMDEV/XRMServices/2011/OrganizationData.svc/AccountSet(guid’3D37ACD3-5846-E011-8AE1-080027131692′)

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

http://crmdev:5555/CRMDEV/XRMServices/2011/OrganizationData.svc/AccountSet(guid'{3D37ACD3-5846-E011-8AE1-080027131692}’)?$select=AccountNumber,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.

http://crm2011odatatool.codeplex.com/

Screen shot of his 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";

$.ajax({
       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
               ProcessReturnedEntities(data.d.results); 

               // Use for a single selected entity
               ProcessReturnedEntity(data.d);

           },
       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:

Xrm.Page.getAttribute("accountnumber").setValue(eval(oneEntity.AccountNumber));

decimal, double fields:

Xrm.Page.getAttribute("new_float").setValue(parseFloat(eval(oneEntity.new_Float)));

integer fields

Xrm.Page.getAttribute("new_integer").setValue(parseInt(eval(oneEntity.new_Integer)));

money fields

Xrm.Page.getAttribute("new_moneyattribute").setValue(parseFloat(eval(oneEntity.new_MoneyAttribute.Value)));

optionset fields

Xrm.Page.getAttribute("new_optionset").setValue(eval(oneEntity.new_OptionSet.Value));

date fields

var fieldValue = eval(oneEntity.new_DateTime);
var dateValue = new Date(parseInt(fieldValue.replace("/Date(", "").replace(")/", ""), 10));
Xrm.Page.getAttribute("new_datetime").setValue(dateValue);

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

Advertisement