CRM 2011 - oData Explained

An exciting new feature of CRM 2011 is the introduction of a REST endpoint which you can use to perform oData based queries against. The location of your endpoint can be identified using the following format: 

http://<server>/<organizationame>/XRMServices/2011/OrganizationData.svc/

Each entity in CRM is represented by a CSDL (Conceptual schema definition language) collection, and each collection uses the EntityName + Set naming convention. For example you would use the following to access the Accounts collection:

http://<server>/<organizationame>/XRMServices/2011/OrganizationData.svc/AccountSet 

To create your query, you will append your criteria to the end of the URI. The following list defines what options you can use:

• $expand - if you want related records to be retrieved with the record or collection
• $filter - expression or function that must evaluate to ‘true’ for a record to be returned
• $orderby - what values are used to order the returned records
• $select - used to limit which columns/fields you wanted returned, default returns all
• $skip - skip a number of records before returning results
• $top - the max number of records to return, similar to TOP in TSQL

$expand
An example of the $expand option could be where you want to return all Contacts that belong to a specific Account, along with the account record itself. To do this all you need to do is specify the relationship name that defines the relationship between the Account and Contact entities.

By default the Account entity has a 1:N relationship named contact_customer_accounts displayed below:

 

So you can use the following oData query which will return the Account record that matches the account guid and will return all contact records.

http://<server>/<organizationame>/XRMServices/2011/OrganizationData.svc/AccountSet?$filter=AccountId eq guid'BA91ACB8-B813-E211-AABC-000C2970FBB2'&$expand=contact_customer_accounts

$filter
The use of the $filter option can be seen above, and below lists additional options you can use in your expression:

• Equal - /AccountSet?$filter=Address1_City eq 'London'
• Not equal - /AccountSet?$filter=Address1_City ne null
• Greater than - /AccountSet?$filter=Revenue/Value gt 1000
• Greater than or equal - /AccountSet?&$filter=Revenue/Value ge 1000
• Less than - /AccountSet?$filter=Revenue/Value lt 1000
• Less than or equal - /AccountSet?$filter=Revenue/Value le 1000
• Logical and - /AccountSet?$filter=Revenue/Value ge 1000 and Address1_City eq 'London'
• Logical or - /AccountSet?$filter=AccountCategoryCode/Value eq 2 or AccountRatingCode/Value eq 1
• Logical Negation - /AccountSet?$filter=(AccountCategoryCode/Value ne null) and not (AccountCategoryCode/Value eq 1)

Option Sets - to filter against option set values you need to use the /Value e.g.
/AccountSet?$filter=AccountCategoryCode/Value eq 1

Entity Reference - to filter against entity references by id you need to use the /Id e.g.
/ContactSet?$filter=ParentCustomerId/Id eq (guid'BA91ACB8-B813-E211-AABC-000C2970FBB2') 

Functions
In combination with the filter option, you can use any of the following functions. For example purposes imagine that the field Address1_City on the Account entity was set to London

• Starts With - /AccountSet?$filter=startswith(Address1_City, 'Lon')
• Sub String Of - /AccountSet?$filter=substringof('Lond', Address1_City)
• Ends With - /AccountSet?$filter=endswith(Address1_City, 'don')

$orderby

Typical order by functionality, you can order by named fields, and by ascending or descending, or both

• Ascending Ordering - /AccountSet?$filter=Name eq 'MyAccount'&orderby=Addess1_City
• Descending Ordering - /AccountSet?$filter=Name eq 'MyAccount'&orderby=Addess1_City desc
• Both - /AccountSet?$filter=Name eq 'MyAccount'&orderby=Addess1_City, Address1_PostalCode desc

$select
Using the select option you can limit which fields you want returned and in which order.
e.g. /AccountSet?$select=Name, Address1_Line1, Address1_Line2, Address1_Line3, Address1_City

$skip
Use this option to skip the first N number of records e.g.
/AccountSet?$filter=Name eq 'Company'$skip=1 

$top
Use this option to select the first N number of records
/AccountSet?$filter=Name eq 'Company'$top=5 

Please note that because we are affectively just building a URL, you can test your queries from within the browser. 

I will shortly follow up this post with a practical example of how you can use the REST endpoint and oData directly from your CRM forms using JavaScript.