The Microsoft Dynamics CRM Blog
News and views from the Microsoft Dynamics CRM Team

Using OData Retrieve in Microsoft Dynamics CRM 2011

Using OData Retrieve in Microsoft Dynamics CRM 2011

  • Comments 8

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

  • 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

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

  • 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!!

  • 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

  • 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?

  • 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?

  • 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

  • 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

Page 1 of 1 (8 items)
Leave a Comment
  • Please add 7 and 6 and type the answer here:
  • Post