Pogo69's Blog

December 1, 2012

CRM 2011 OData, REST and the QueryString

Filed under: CRM, Javascript — pogo69 [Pat Janes] @ 12:06

Introduction

As with most CRM developers, I have loved the introduction of the newly introduced OData endpoint in CRM 2011.  It provides a simple, yet highly powerful mechanism to perform basic CRUD operations against a CRM 2011 Organisation.

Zen, and the Art of OData Query Construction

What makes working with REST so easy from client applications (Javascript and Silverlight in the case of a CRM 2011 developer), is that operations are performed by constructing an appropriate address via an HTTP Request’s QueryString.

A corollary of the implementation mechanism of a “Restful” web service, is that we can “play” with our queries directly in Internet Explorer.  I did this recently (actually I do it all the time; but I digress) to demonstrate to a fellow developer how to construct an OData query; the following is a reconstruction of that demonstration.

The Endpoint

In order to address the OData Endpoint, you must navigate to the following address:

http://<OrganisationRoot>/XrmServices/2011/OrganizationData.svc

Authentication

NB: The OData endpoint is not supported for use outside of Javascript and Silverlight within the CRM deployment.  Thus, in order to address the OData endpoint from the Internet Explorer QueryString, you must first successfully authenticate with the CRM Organisation.

If you are connecting from within the internal network on which the CRM is deployed, it is likely that you will be able to connect automatically due to Windows Integrated Security.  Otherwise, you may first have to open CRM in another tab.

EntitySet

Navigation to the root of the OData endpoint results in a collection of EntitySets being returned.  The collection is a list of the Entity Types on which OData operations may be performed.

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<service xmlns="http://www.w3.org/2007/app" xmlns:app="http://www.w3.org/2007/app" xmlns:atom="http://www.w3.org/2005/Atom" xml:base="http://<OrganisationRoot>/XRMServices/2011/OrganizationData.svc/">
    <workspace>
        <atom:title>Default</atom:title>
        <collection href="OpportunityCompetitorsSet">
            <atom:title>OpportunityCompetitorsSet</atom:title>
        </collection>
        <collection href="SystemFormSet">
            <atom:title>SystemFormSet</atom:title>
        </collection>
        <collection href="RoleSet">
            <atom:title>RoleSet</atom:title>
        </collection>
        ...
    </workspace>
</service>

OpportunitySet

The Query that we were building can be defined as follows:

“When was the most recently Won Opportunity attached to the current Account?”

So, we need to interrogate the OpportunitySet.  We do this by simply appending a forward-slash and the name of the EntitySet to the end of the address:

http://<OrganisationRoot>/XrmServices/2011/OrganizationData.svc/OpportunitySet

This will return a collection of every Opportunity in the system (with ALL of each Opportunity’s attributes):

<?xml version="1.0" encoding="utf-8" standalone="yes" ?>
<feed xml:base="http://<OrganisationRoot>/XRMServices/2011/OrganizationData.svc/" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom">
  <title type="text">OpportunitySet</title>
  <id>http://<OrganisationRoot>/XrmServices/2011/OrganizationData.svc/OpportunitySet</id>
  <updated>2012-11-30T21:44:25Z</updated>
  <link rel="self" title="OpportunitySet" href="OpportunitySet" />
  <entry>
    <id>http://<OrganisationRoot>/XRMServices/2011/OrganizationData.svc/OpportunitySet(guid'9cdd7cfd-6a34-e211-bea2-00012e244939')</id>
    <title type="text">Test</title>
    <updated>2012-11-30T21:44:25Z</updated>
    <author>
      <name />
    </author>
    <link rel="edit" title="Opportunity" href="OpportunitySet(guid'9cdd7cfd-6a34-e211-bea2-00012e244939')" />
    <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/opportunitycompetitors_association" type="application/atom+xml;type=feed" title="opportunitycompetitors_association" href="OpportunitySet(guid'9cdd7cfd-6a34-e211-bea2-00012e244939')/opportunitycompetitors_association" />
    ...
    <category term="Microsoft.Crm.Sdk.Data.Services.Opportunity" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
    <content type="application/xml">
      <m:properties>
        <d:ParticipatesInWorkflow m:type="Edm.Boolean">false</d:ParticipatesInWorkflow>
        <d:new_IsParentOngoingOpp m:type="Edm.Boolean">false</d:new_IsParentOngoingOpp>
        <d:TimeZoneRuleVersionNumber m:type="Edm.Int32" m:null="true" />
        ...
      </m:properties>
    </content>
  </entry>
</feed>

<entry>

Each <entry> node represents an Entity instance; in this case, an Opportunity.

<link>

The <link> nodes beneath <entry> represent child Entity Collections attached to the individual Entity instance.

<content><m:properties><d:AttributeSchemaName /></m:properties></content>

Each property is represented by a child node of <content><m:properties>.  The attribute nodes are named with the SchemaName of the attribute as defined in your CRM Customisations; the data type is represented by the m:type attribute.

OData Operators

$filter

The $filter operator provides a mechanism to “filter” the returned result set; much like the where clause in SQL.

When was the most recently Won Opportunity attached to the current Account?”

So, we need to return only Won Opportunities for the current Account.  Thus, our filter will look like the following:

/OpportunitySet?$filter=StatusCode/Value eq 3 and CustomerId/Id eq (guid’00000000-0000-0000-0000-000000000000′)

In other words, return all Opportunities with a StatusCode of 3 (Won) and a CustomerId GUID identifier matching that specified.  See the CRM SDK and the OData Specification for additional details regarding the $filter syntax.

$top and $orderby

When was the most recently Won Opportunity attached to the current Account?”

We need the most recently Won Opportunity.  For the purposes of our query, most recently Won is defined as the most recent Actual Close Date.  Thus we need to order our result set by Actual Close Date descending; and return only the top 1 entry:

/OpportunitySet?$orderby=ActualCloseDate desc&$top=1

$select

When was the most recently Won Opportunity attached to the current Account?”

In order to determine when the most recently Won Opportunity occurred, we only care about the Actual Close Date.  With the $select operator, we can choose which attributes get returned in the result set:

/OpportunitySet?$select=ActualCloseDate

Consolidated Query

If we put the query operators together, we end up with:

/OpportunitySet?$filter=StatusCode/Value eq 3 and CustomerId/Id eq (guid’00000000-0000-0000-0000-000000000000′)&$top=1&$orderby=ActualCloseDate desc&$select=ActualCloseDate

The result set will look something like:

<?xml version="1.0" encoding="utf-8" standalone="yes" ?>
<feed xml:base="http://dev.mrcrm.com.au:8888/MrCRM/XRMServices/2011/OrganizationData.svc/" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom">
  <title type="text">OpportunitySet</title>
  <id>http://dev.mrcrm.com.au:8888/MrCRM/XrmServices/2011/OrganizationData.svc/OpportunitySet</id>
  <updated>2012-12-01T01:29:33Z</updated>
  <link rel="self" title="OpportunitySet" href="OpportunitySet" />
  <entry>
    <id>http://dev.mrcrm.com.au:8888/MrCRM/XRMServices/2011/OrganizationData.svc/OpportunitySet(guid'3b225dd4-28e3-e011-8abe-001517418134')</id>
    <title type="text">5 User Hosted</title>
    <updated>2012-12-01T01:29:33Z</updated>
    <author>
      <name />
    </author>
    <link rel="edit" title="Opportunity" href="OpportunitySet(guid'3b225dd4-28e3-e011-8abe-001517418134')" />
    <category term="Microsoft.Crm.Sdk.Data.Services.Opportunity" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
    <content type="application/xml">
      <m:properties>
        <d:ActualCloseDate m:type="Edm.DateTime">2012-03-18T14:00:00Z</d:ActualCloseDate>
      </m:properties>
    </content>
  </entry>
</feed>

If the result set is empty, there were NO Won Opportunities attached to the current Account.  Otherwise, the <d:ActualCloseDate /> node contains our answer.

Javascript – How to Use our Query

In the interest of completeness, I will quickly show how to use our query from a Javascript Web Resource (code assumes the existence and availability of the JSON and jQuery libraries.

var serverURL = Xrm.Page.context.getServerUrl();
var accountId = Xrm.Page.data.entity.getId().replace(/[\{\}]/, "");
var query = serverURL + "/OpportunitySet?$filter=StatusCode/Value eq 3 and CustomerId/Id eq (guid'" + accountId + "')&
$top=1&$orderby=ActualCloseDate desc&$select=ActualCloseDate";
$.ajax({
  type: "GET",
  contentType: "application/json; charset=utf-8",
  datatype: "json",
  url: query,
  beforeSend: function (XMLHttpRequest) {
    XMLHttpRequest.setRequestHeader("Accept", "application/json");
  },
  success: function (data, textStatus, XmlHttpRequest) {
    var results = data.d.results;

    if (results.length == 0) {
      alert("No Won Opportunities found!!");
    }
    else {
      var dateResult = results[0].ActualCloseDate;
    }
  },
  error: function (XmlHttpRequest, textStatus, errorThrown) {
    alert('OData Select Failed: ' + odataSelect);
  }
});

See the SDK for more information on the idiosyncrasies of date processing from OData:

http://msdn.microsoft.com/en-us/library/gg328025.aspx#BKMK_WorkingWithDates

May 5, 2011

The Beginnings of a Date Library

Filed under: Cutting Code, Javascript — pogo69 [Pat Janes] @ 09:34

Recently in one of the CRM Forums, there was a request for some javascript to find the differences between two dates.  I’d started to work up a JScript Date Library that does exactly that, so I figured I’d share it here for all:

if (typeof DateLib == 'undefined') {
	DateLib = {};
}

DateLib.Diff = function (from, to) {
	return to - from;
}
DateLib.DiffMinutes = function (from, to) {
	return DateLib.Diff(from, to) / (1000 * 60);
}
DateLib.DiffParts = function (from, to) {
	var diff = DateLib.DiffMinutes(from, to);

	var parts = new Object();

	parts.Days = Math.floor(diff / (60 * 24));
	diff -= (parts.Days * 60 * 24);
	parts.Hours = Math.floor(diff / 60);
	diff -= (parts.Hours * 60);
	parts.Minutes = Math.floor(diff);

	parts.Display =
			(parts.Days > 0 ? parts.Days + " days, " : "") +
			(parts.Hours > 0 ? parts.Hours + " hrs, " : "") +
			(parts.Minutes + " mins");

	return parts;
}

Blog at WordPress.com.