Tuesday, March 8, 2011

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.

image

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.
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";

$.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.

10 comments:

Anonymous said...

Excellent post. I wish I had read this before I used oData in CRM 2011 myself for the first time.

You will save people a lot of time if they use this blog

Unknown said...

Great post. However, I can't get the data assigned to a field in CRM, no matter what I try. I have use your example to the letter, but the script won't work. I may be missing something on the data.d format. Here is my code:

function GetRate(context) {

var oProduct = context.getEventSource().getValue()[0].id;
var oPricelist = Xrm.Page.getAttribute("ews_pricelistid").getValue()[0].id;

var pagecontext = Xrm.Page.context;
var serverUrl = pagecontext.getServerUrl();
var oDataPath = serverUrl + "/XRMServices/2011/OrganizationData.svc";
var oDataSelect = oDataPath + "/ProductPriceLevelSet?$select=Amount&$filter=PriceLevelId/Id eq guid'" + oPricelist + "' and ProductId/Id eq guid'" + oProduct + "'";

alert(oDataSelect);

$.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) {
ProcessData(data.d);
},
error: function (XmlHttpRequest, textStatus, errorThrown) {
alert('OData Select Failed: ' + oDataSelect);
}
});
}

function ProcessData(query) {
alert("Started ProcessData");
var oData = query;
var amount = oData.Amount;
var amountValue = eval(amount.Value);

alert(amountValue);
Xrm.Page.getAttribute("ews_rate").setValue(parseFloat(eval(Amount)));
}

Mark Kovalcson said...

Guillermo is ews_rate a CRM Money value or a decimal or double?

Luiz said...

Thanks Mark,
The best post I found. Really great.
One question. I have a global variable that I want to set in the ProcessReturnedEntities function during the OnLoad event. I need to use this value to load some aspx pages later. I can put an alert and see that the value is being returned but the value is lost somewhere after the call.

For instance,

in the Onload event I have:

var webSiteURL = "";
alert("2: " + webSiteURL);
.....
function ProcessReturnedEntities(ManyEntities)
{
var oneEntity = ManyEntities[0];
webSiteURL = eval(oneEntity.AccountURL);
alert("1: " + webSiteURL);
}

Alert 1 shows the correct value.
Alert 2 losts the correct value.

What am I doing wrong?

Thanks

Hugo said...

Fantastic explanation. Thank you so much. You should write a book for CRM 2011 developers !

Mark Kovalcson said...

Thanks! I'm typically covered up with interesting projects so getting my blog articles posted is probably the closest I will ever come to writing a book.

David said...

Great post. I struggled to get date fields working and after debugging used:
var fieldValue = oneEntity.new_DateTime; var dateValue = new Date(parseInt(fieldValue.replace("/Date(", "").replace(")/", ""), 10));

since replace method was not otherwise exposed

SharePoint Development Australia Page said...

Nice post, thanks for sharing this wonderful and useful information with us.

Greg said...

Excellent post. Thank you.

For everyone who is missing the lookup field I came up with this code:

var olookup = new Object();
olookup.id = ManyEntities[0].Id;
olookup.entityType = "account";
olookup.name = ManyEntities[0].Name;
var olookupValue = new Array();
olookupValue[0] = olookup;
Xrm.Page.getAttribute("account").setValue(olookupValue);

Summer said...

great post!!! everything I need in one place!!