Microsoft CRM 2011 oData Examples

Microsoft CRM has added support for oData in the 2011 release. oData, according to the oData web site “…is a Web protocol for querying and updating data that provides a way to unlock your data and free it from silos that exist in applications today.” What it means is that using an XMLHttpRequest object, you can send a query string to a web service with syntax that is very SQL like and get data back from the web service. It goes beyond that in that it also allows you to update and insert as well. MS CRM did not implement the full oData specification, but it still offers a quite a bit of functionality.
My goal here is to give you two different oData calls to populate some data on a CRM form. I will assume that you have a basic knowledge of CRM, adding JavaScript to forms in 2011 and creating an XMLHttpRequest. Also, I am using jQuery in the form to parse the json that gets returned.
Here’s the scenario that I’m going to implement. On the account main page/detail screen, you need to put data from the contact. I know, I know, contacts appear in a grid, but the customer really wants to see the info for the support contact (a custom attribute I created on the contact) and the most recently created contact at the top of the account screen like this:

Be sure to read the comments in the Javascript – I have a lot of explanations about the oData query in the comments. If you don’t read those, you’ll miss important stuff. Also, remember, this is just a quick intro. Be sure to read the oData URI Conventions to learn more about some of the various query options, too!
I’ve made the solution available for download, but if you want to create the customizations manually, the steps are below.

1. Create a solution
2. On the account entity, create the following custom attributes:
   1. new_MostRecentEmail
   2. new_MostRecentFullName
   3. new_MostRecentPhone
   4. new_MostRecentTitle
   5. new_SupportEmail
   6. new_SupportFullName
   7. new_SupportPhone
   8. new_SupportTitle
3. On the contact entity create the following custom attribute:
   1. new_IsSupportContact
4. Add two script web resources
   1. Create one and add jQuery (you can download jQuery here) for the code
   2. Create a second resource and add the javascript code further down
5. Modify the account form to look like the picture above using the fields created above. Make the added fields read-only.
6. On the account form, add both script resources.
7. On the form properties, for the form OnLoad event, specify the “onLoad” function in the second script resource you created.
8. Create a test account and a test contact. Designate one or more contacts as “Is Support Contact”.
9. Publish your customizations and try it out.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77

var xp = Xrm.Page;    function onLoad(context) {     var accountId = xp.data.entity.getId();        /*  This is the heart of using oData. This is where the query is defined.         It has very SQL-like syntax although instead of a "where" clause it         uses "filter". Also, the comparison operators or different. 'eq' instead         of '=', 'gt' instead of '>", etc.             I'm using the oData select keyword to select the four columns I'm interested in.         Next, I'm using the "top" construct to return a single record.         After the top is the "orderby" which sorts on "CreatedOn" descending         so the most recent created record is first. Between the top 1 filter and         the sort of ModifiedOn descending, the record we want is returned.            Also note that the ParentCustomerId is a complex type, so you have to         specify 'ParentCustomerId/Id' to navigate from the ParentCustomerId         to it's child property 'Id' to get to the actual value.*/         var mostRecentQuery = "/XRMServices/2011/organizationData.svc/ContactSet?$select=FullName,JobTitle,EMailAddress1,Telephone1&$top=1&$orderby=CreatedOn desc&$filter=ParentCustomerId/Id eq guid'" + accountId + "'";     getContact(mostRecentQuery, "MostRecent");        var supportContactQuery = "/XRMServices/2011/organizationData.svc/ContactSet?$select=FullName,JobTitle,EMailAddress1,Telephone1&$filter=new_IsSupportContact eq true";     getContact(supportContactQuery, "SupportContact"); }    function getContact(oDataQuery, queryName) {     try {             var serverUrl;              /* This is the heart and soul of the whole thing - the actual oData call. */             serverUrl = Xrm.Page.context.getServerUrl() + oDataQuery;             var request = new XMLHttpRequest();             request.open("GET", serverUrl, true);             request.setRequestHeader("Accept", "application/json");             request.setRequestHeader("Content-Type", "application/json; charset=utf-8");             /* Next, we specify the function that we want called when the XMLHttpRequest state                changes. This will get called on each state change. */             request.onreadystatechange = function () {                 requestComplete(request, queryName);             }             request.send();     } catch (e) {         // You probably want to do something other than an alert here.         alert(e.Description);     } }    function requestComplete(request, queryName) {     /*  A request.readyState of 4 means the request is complete. If         it's complete and the status is 200 (OK), then we can assign the results         of the call to our controls on the form.*/     if (request.readyState == 4 && request.status == 200) {         // debugger;         var json = $.parseJSON(request.responseText);         if ( (json != undefined) && (json.d != undefined) && (json.d.results != undefined) && (json.d.results[0] != null) ) {             json = json.d.results[0];             // Note that the way things are set up, it's possible to have more             // than one "Support Contact" returned but I'm hard coding the results             // to return the first contact only.             if (queryName == "MostRecent") {                 xp.getAttribute("new_mostrecentfullname").setValue(json.FullName);                 xp.getAttribute("new_mostrecentphone").setValue(json.Telephone1);                 xp.getAttribute("new_mostrecentemail").setValue(json.EMailAddress1);                 xp.getAttribute("new_mostrecenttitle").setValue(json.JobTitle);                 // As an aside (the field below doesn't exist), when placing values                 // into a numeric field, you will need to use the javascript                 // function parseFloat to prevent CRM type errors.                 //xp.getAttribute("new_numericrank").setValue(parseFloat(json.new_NumericRank));             } else if (queryName == "SupportContact") {                 xp.getAttribute("new_supportfullname").setValue(json.FullName);                 xp.getAttribute("new_supportphone").setValue(json.Telephone1);                 xp.getAttribute("new_supportemail").setValue(json.EMailAddress1);                 xp.getAttribute("new_supporttitle").setValue(json.JobTitle);             }         }     } }

P.S. – I noticed right before I posted that the “Is Support Contact Query” doesn’t filter by account, so the same contact will appear regardless of what account you’re looking at. Consider it an exercise for the reader to add that filter.