Using OData Retrieve in Microsoft Dynamics CRM 2011


With the release of Microsoft Dynamics CRM 2011, we have added a new Windows Communication Foundation (WCF) data services (ODATA) endpoint. The endpoint facilitates CRUD operation on entities via scripts using Atom or Json format. In this blog I would be talking about some of the considerations when using the endpoint, specifically around the use of retrieves.

First, the operations supported over this endpoint are limited to create, retrieve, update and delete. The REST philosophy does not support other operations and so we followed J. We did not implement others since the story around service operations is not fully developed in the current WCF data services offering.

The $format and $inlinecount operators are not supported. We only support the $filter, $select, $top, $skip, $orderby

Some of the restrictions when using the implemented operators are.

Operator

Restrictions

$expand

· Max expansion 6

$top

· Page size is fixed to max 50 records

· $top gives the total records returned across multiple pages

$skip

· When using with distinct queries, we are limited to the total (skip + top) record size = 5000.

· In CRM the distinct queries does not use paging cookie are and so we are limited by the CRM platform limitation to the 5000 record.

$select

· One level of navigation property selection is allowed I.e.

…/AccountSet?$select=Name,PrimaryContactId,account_primary_contact

…/AccountSet?$select=Name,PrimaryContactId,account_primary_

contact/LastName&$expand=account_primary_contact

$filter

· Conditions on only one group of attributes are allowed. By a group of attribute I am referring to a set of conditions joined by And/Or clause.

· The attribute group may be on the root entity

…/TaskSet?$expand=Contact_Tasks&$filter=Subject eq ‘test’ and Subject ne null

· (or) on the expanded entity.

…/TaskSet?$expand=Contact_Tasks&$filter=Contact_Tasks/FirstName eq ‘123‘

· Arithmetic, datetime and math operators are not supported

· Under string function we support Substringof, endswith, startswith

$orderby

· Order are only allowed on the root entity.

Navigation

· Only one level of navigation is allowed in any direction of a relationship. The relationship could be 1:N, N:1, N:N

Cheers,

Shashi Ranjan

Comments (13)

  1. Richard Barclay says:

    Great post. The OData endpoint allows you to access an whole host of records and relationships.

    I've blogged details on how to find the relationships with a few examples

    http://intergr8it.net/?p=150

  2. Hosk says:

    It took me a while to get used to OData, particularly adding the relationships but once you get your head around the syntax it's great.  The real benefit is you can test the OData queries in the browser, making it easy and quick to test.

    I hope to blog about this in the near future.

  3. Rhett Clinton says:

    Please check out the CRM 2011 OData Query Designer available on codeplex. Here is the blog post bingsoft.wordpress.com/…/crm-2011-odata-query-designer-crm2011

    Enjoy!!

  4. test says:

    xxx.crm.dynamics.com/…/OrganizationData.svc

    what url one has to feed to powerpivot to get online crm 2011 data e.g

    list of all accoutns and opportunities

  5. chuck@upsidecommerce.com says:

    I need to create a connection string to a CRM 2011 Online service that is to support a Microsoft Data Feed Provider for 64-bit PowerPoint in 64-bit Excel 2010 on 64-bit Windows 7. The CRM service's URL uses SSL, and the CRM service depends upon Windows Live to authenticate end users. Given these security measures, how is a connection created when using the 'From Data Feeds' Table Import Wizard. The Advanced option does not appear to support authentication by Windows Live (or any other Identity Provider). And entering the Base Url as, for example, validnameofcrmservice.crm.dynamics.com/OrganizationData.svc throws an error which hangs the Excel application. Perhaps there is an existing Atom Service document, or Atom Feed document that I can edit to support my situation?

  6. chuck@upsidecommerce.com says:

    I need to create a connection string to a CRM 2011 Online service that is to support a Microsoft Data Feed Provider for 64-bit PowerPoint in 64-bit Excel 2010 on 64-bit Windows 7. The CRM service's URL uses SSL, and the CRM service depends upon Windows Live to authenticate end users. Given these security measures, how is a connection created when using the 'From Data Feeds' Table Import Wizard. The Advanced option does not appear to support authentication by Windows Live (or any other Identity Provider). And entering the Base Url as, for example, validnameofcrmservice.crm.dynamics.com/OrganizationData.svc throws an error which hangs the Excel application. Perhaps there is an existing Atom Service document, or Atom Feed document that I can edit to support my situation?

  7. Alex James says:

    Charles,

    This does indeed look like an authentication problem. Today PowerPivot doesn't suport LiveID so it will be getting an 'Access Denied' response from the server.

    I'm intimately aware of this problem, in fact I'm currently trying to come up with guidance for OData Services (including all Microsoft ones going forward) that will give them a chance of interoperating with clients like PowerPivot in the future.

    So while things aren't great today I'm hopeful for the future.

    -Alex

  8. Juan says:

    Lets say I have a ContactType attribute I want to filter using the example above will be something like …/TaskSet?$expand=Contact_Tasks&$filter=Contact_Tasks/ContactType/Value eq 803270000, this doesn't work, how would be the correct syntax?

    Thanks!

  9. Mike Hammons says:

    Here we are almost 2 years later and still cannot make a simple OData connection in PowerPivot to CRM Online. It's all due to the somewhat "proprietary" authentication mechanism. Note even with the latest UR12 release. Microsoft, when o when will we be able to use some simple tools to connect CRM Online. Please make this more industry standard. You will see even higher growth with CRM if you do. This stuff is super easy in other online CRM applications…hint hint….initials something like sfdc.

    This causes a host of similar problems with easily connecting to other services like Zapier, Iftt, ConnectionCloud, Basecamp, SharePoint, Evernote, Azure, Paypal…literally hundreds of value added online applications.

  10. crmonce.com says:

    good

  11. Pradeep Chauhan says:

    I wanted to use it with Tableau Desktop but it is giving

    Bad OData Error.

  12. nicalaus.west@lee.net says:

    I found the post mentioned below while looking into doing a little work using the OData endpoint and thought that it would be a good followup to this one. Happy New Year!

    niiranen.eu/…/connecting-crm-online-odata-feed-excel-2013-power-query