CRM 2011 – Getting started with oData

I was asked today where the oData url was for CRM.  it’s always tricky when someone asks you expecting you to know the oData url off the top of your head.

but CRM has this value easily at hand, you just need to know where to find it

Settings–> Customizations –> Developer Resources

When you click on here you will then get to see your discovery service, organisation service and oData Service.

The oData service should look something like this

http://<server name:port>/Metaphorix/XRMServices/2011/OrganizationData.svc

Now you have the oData url you are at the beginning of your quest.  The first thing you should do is download the oData query tool created by MVP Rhett Clinton

This article explains some of the filters you can add to oData queries

Using OData Retrieve in Microsoft Dynamics CRM 2011

This technet article is fantastic and has a lot of explanation and examples

OData System Query Options Using the REST Endpoint

another blog post, if you are still hungry for more information

CRM 2011 OData, JSON and CRM Forms

The great thing about oData is you can test the query in the internet browser, although when I did it in IE it didn’t show the full result but when I ran it in Chrome, after logging in it returned the XML data returned in the query.


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

CRM 2011 – OData Query tester

I have done some work with oData which I will blog about at a later date.  Initially I found oData quite difficult to understand, especially when you are accessing it using Javascript.

but after I got over the initial learning hump I found I really liked it.  The best part of oData is you can test any of your queries by pasting them into your internet browser and it returns the data straight away.  I ended up creating an excel spreadsheet to create queries.

Today I saw this blog entry CRM 2011 OData Query Designer by Rhett Clinton, who recently also made the medadata browser for CRM 2011 as well.

You can also download this code from codeplex.  It’s a very useful tool because oData can take a little bit of time to understand and this tool will allow you to easy create oData queries without having to get the syntax correct.

Excellent work from Rhett.  You can see all the tools he has made for CRM 2011 at the top of his blog.