Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

OData System Query Options Using the REST Endpoint

[Applies to: Microsoft Dynamics CRM 2011]

You can refine the results of your query by using system query options. The following table lists the query string options defined in the OData protocol that are implemented in the REST endpoint for Microsoft Dynamics CRM 2011.

 

Option Description

$expand

Directs that related records should be retrieved in the record or collection being retrieved.

$filter

Specifies an expression or function that must evaluate to ‘true’ for a record to be returned in the collection.

$orderby

Determines what values are used to order a collection of records.

$select

Specifies a sub set of properties to return.

$skip

Sets the number of records to skip before it retrieves records in a collection.

$top

Determines the maximum number of records to return.

$expand

Directs that related records should be retrieved in the record or collection being retrieved.

If you want to retrieve related records, locate the name of the entity relationship that defines this relationship. You may have to view the entity relationship information in the application to correctly identify the relationship or the conceptual schema definition language (CDSL) for the Organization Data Service.

For example, to retrieve opportunity records related to accounts, use the opportunity_customer_accounts entity relationship. The query /AccountSet?$expand=opportunity_customer_accounts returns the opportunity records and the account records.

If you are limiting the columns returned, you must also include the name of the navigation property in the query. For example, the query /AccountSet?$select=Name,opportunity_customer_accounts&$expand=opportunity_customer_accounts returns only the account name and the expanded opportunity records.

Microsoft Dynamics CRM 2011 does not support querying a multi-level relationship property. For example, this query, intended to retrieve information about the owning user of opportunities related to accounts, will not work: /AccountSet?$expand=opportunity_customer_accounts/opportunity_owning_user.

By default, you can define up to six relationships to expand. This query, expanding both the opportunities and system user that owns the account, will work, /AccountSet?$select=Name,opportunity_customer_accounts,user_accounts&$expand=opportunity_customer_accounts,user_accounts.

noteNote
The number of related records returned is subject to the same limitation on the number of records returned. For more information, see Limitations on number of records returned. You can increase the number of relationships to expand by changing the MaxExpandCount setting value. For more information, see ServerSettings Table Metadata (Advanced Settings).

For more information, see OData: Filter System Query Option ($expand).

$filter

Specifies an expression or function that must evaluate to true for a record to be returned in the collection.

If you are retrieving additional sets of data using the next link you should not change the $filter query option value because this causes unpredictable results.

The OData specification for the Filter System Query Option describes the operators used to create an expression to evaluate in the filter. Microsoft Dynamics CRM uses all the logical operators and a subset of the functions available. Microsoft Dynamics CRM does not support use of the arithmetic operators. Filters can use grouped conditions indicated by parentheses. Comparisons of time values must use the following format: datetime’<time value>’, for example datetime'2010-07-15' or datetime'2010-07-15T16:19:54Z'.

The following table lists the operators supported by Microsoft Dynamics CRM.

 

Operator Description Example

eq

Equal

/AccountSet?$filter=Address1_City eq 'Redmond'

ne

Not equal

/AccountSet?$filter=Address1_City ne null

gt

Greater than

/AccountSet?$filter=CreditLimit/Value gt 1000

ge

Greater than or equal

/AccountSet?&$filter=CreditLimit/Value ge 1000

lt

Less than

/AccountSet?$filter=CreditLimit/Value lt 1000

le

Less than or equal

/AccountSet?$filter=CreditLimit/Value le 1000

and

Logical and

/AccountSet?$filter=CreditLimit/Value ge 1000 and Address1_StateOrProvince eq 'TX'

or

Logical or

/AccountSet?$filter=AccountCategoryCode/Value eq 2 or AccountRatingCode/Value eq 1

not

Logical Negation

/AccountSet?$filter=(AccountCategoryCode/Value ne null) and not (AccountCategoryCode/Value eq 1)

You can use the $filter query option to filter for the value of an EntityReference attribute. For example, if you have the Id value for a SystemUser record, you can retrieve a list of all the account records that a user is assigned by using the following filter:

/AccountSet?$filter=OwnerId/Id eq (guid'" + SystemUserId + "')

The following table lists the functions supported by Microsoft Dynamics CRM.

 

Function Example

startswith

/AccountSet?$filter=startswith(Name, 'a')

substringof

/AccountSet?$filter=substringof('store',Name)

endswith

/AccountSet?$filter=endswith(Name, '(sample)')

For more information, see OData: Filter System Query Option ($filter).

$orderby

Determines what values are used to order a collection of records. By default, the order is ascending. Use desc to reverse the order or asc to explicitly set the default.

If you are retrieving additional sets of data using the next link you should not change the $orderby query option value because this causes unpredictable results.

noteNote
You can select up to 12 columns using $orderby.

When ordering by complex types, such as EntityReference or OptionSetValue, only the attribute name should be specified. For example, to order by the primary contact of an account, use the query $orderby=PrimaryContactId. Specifying $orderby=PrimaryContactId/Name will give you a Bad Request HTTP error (HTTP Error Code 400).

The following sample lists account records by country in ascending order, and by city in descending order:

/AccountSet?$select=Address1_Country,Address1_City,Name&$orderby=Address1_Country,Address1_City desc&$filter=(Address1_Country ne null) and (Address1_City ne null)

For more information, see OData: Filter System Query Option ($orderby).

$select

Specifies a subset of properties to return and the order in which the columns of data will be organized. The default is to return all columns that corresponds to $select=*.

If you are using $expand to include related data and you are using $select to limit the columns returned, you must also include the name of the navigation property in the query. For example, the following query, /AccountSet?$select=Name,opportunity_customer_accounts&$expand=opportunity_customer_accounts, will return only the account name and the expanded opportunity records.

noteNote
There is a maximum path length of 2048 characters for POST and GET requests in Internet Explorer. You can easily reach this limitation if you were to include every attribute in the account entity in the $select option. For more information, see Microsoft KB 208427

For more information, see OData: Filter System Query Option ($select).

$skip

Sets the number of records to skip before retrieving records in a collection.

If you are retrieving additional sets of data using the next link you should not change the $skip query option value because this causes unpredictable results.

For more information, see OData: Filter System Query Option ($skip).

$top

Determines a maximum number of records to return.

If you are retrieving additional sets of data using the next link you should not change the $top query option value because this will cause unpredictable results.

For more information, see OData: Filter System Query Option ($top).

Unsupported System Query Options

Microsoft Dynamics CRM 2011 does not support using the following system query options:

  • $inlinecount

  • $count

  • $format

See Also

Microsoft Dynamics CRM 2011
Send comments about this topic to Microsoft.
© 2013 Microsoft Corporation. All rights reserved.

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.