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:
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
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
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)
/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')
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')
- 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
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
Use this option to skip the first N number of records e.g.
/AccountSet?$filter=Name eq 'Company'$skip=1
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.