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.


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.

Friday, March 4, 2011

CRM 2011 Set Default Transaction Currency in JavaScript

The following function uses JSON and OData to select the first Currency entity that is configured on your system and then sets the transactioncurrencyid lookup to be that currency.

This particular feature is just as relevant for CRM 2011 as it was for CRM 4.0, but it is easier to implement now.

The OData selection for this consists of the following:

If you have multiple currencies, you can always add a filter to OData select to grab the specific currency that you need. This function uses the first currency returned.

function SetDefaultCurrency() { var context = Xrm.Page.context; var serverUrl = context.getServerUrl(); var ODataPath = serverUrl + "/XRMServices/2011/OrganizationData.svc"; var odataSelect = ODataPath + "/TransactionCurrencySet?$select=TransactionCurrencyId,CurrencyName"; $.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) { var myCurrency = data.d.results[0]; var idValue = eval('myCurrency.TransactionCurrencyId'); var textValue = eval('myCurrency.CurrencyName'); var thisEntityType = 'transactioncurrency'; Xrm.Page.getAttribute("transactioncurrencyid").setValue([{ id: idValue, name: textValue, entityType: thisEntityType }]); }, error: function (XmlHttpRequest, textStatus, errorThrown) { alert('OData Select Failed: ' + odataSelect); } }); }

In order to use this. You need to make sure that you include jquery1.4.1.min.js and json2.js as libraries first since this script relies on them. Create another library with the above function. If you just call the SetDefaultCurrency function in the OnLoad event of your form, the currency will be set as the form opens and will be be ready for you to set any currency values that rely on this.


The jquery1.4.1.min.js and json.js files can be found in the most recent MS CRM SDK. sdk\samplecode\js\restendpoint\jqueryrestdataoperations\jqueryrestdataoperations\scripts

Wednesday, March 2, 2011

CRM 2011 RTM VM Installation Differences

There are a few differences between the RTM installation and the Beta installation I have documented in earlier blog articles. Starting with the following: http://crmscape.blogspot.com/2010/09/creating-ms-crm-2011-vm-part-1-of-2.html

1. There are minor appearance changes in the CRM 2011 Installation screens, but everything else looks close enough that the Beta instructions still work.

2. The entire section of my instructions called SQL Reporting Services ( Configure Data Source ) is unnecessary, which is an improvement.  However I did add my Admin user account to the PrivUserGroup and PrivReportingGroup and then everything worked great.

blog_Admin properties

Otherwise the instructions still worked well for me. I’ll look over the updated installations instructions shortly to make sure there isn’t anything of importance that needs to be added.

I’m also using the latest Virtual Box 4.0.4 release for this which is working well. The only reason I used it instead of VMWare is that I had an existing VM just waiting for MS CRM to be installed with all the prerequisites in place.

I’ve been “living on a cloud” for the past few months in CRM Online and Windows/SQL Azure land, but now that the 2011 On Premises release is out, I’m back on terra firma working with that again too.