Adding Selection Criteria to REST Queries in CRM 2011
When querying the REST end point you are able to:
- specify a filter to limit which records are returned,
- request which fields should be returned,
- specify ordering, and
- specify joins
For help writing these scripts there’s an awesome tool available on Codeplex: OData Query Designer, which has now been bundled into the Dynamics XRM Tools solution on Codeplex.
I recommend working out your REST query using the above tool and testing in Internet Explorer before you attempt to write your REST query in jscript.
I find when I form my URL in jscript I need to replace blank spaces in the query section of the URL with “%20”, e.g.:
// Define ODATA query
var ODATA_ENDPOINT = "/XRMServices/2011/OrganizationData.svc";
var ODATA_EntityCollection = "/PriceLevelSet";
var PriceListName = 'Wholesale Price List';
var QUERY = "?$select=PriceLevelId&$filter=Name%20eq%20'" + PriceListName + "'&$top=1";
var URL = serverUrl + ODATA_ENDPOINT + ODATA_EntityCollection + QUERY;
When testing in Internet Explorer you need to lose the %20 and just have the space. The OData Query Designer tool outputs URLs that will work in Internet Explorer.
Here’s a full example of a working REST query that selects a single field, uses a filter and performs a top n. In this example (intended for the Opportunity form’s Onload event) I execute a REST query to retrieve the GUID of the Price List named “Wholesale Price List”. As this uses REST your CRM form will need json2 and jquery libraries registered on the CRM form (I have these libraries in a solution file I import when needed):
function RetrieveGUID() {
// Get CRM Context
var context = Xrm.Page.context;
var serverUrl = context.getServerUrl();
// Cater for URL differences between on-premise and online
if (serverUrl.match(/\/$/)) {
serverUrl = serverUrl.substring(0, serverUrl.length - 1);
}
// Define ODATA query
var ODATA_ENDPOINT = "/XRMServices/2011/OrganizationData.svc";
var ODATA_EntityCollection = "/PriceLevelSet";
var PriceListName = 'Wholesale Price List';
var QUERY = "?$select=PriceLevelId&$filter=Name%20eq%20'" + PriceListName + "'&$top=1";
var URL = serverUrl + ODATA_ENDPOINT + ODATA_EntityCollection + QUERY;
//Asynchronous AJAX call
$.ajax({
type: "GET",
contentType: "application/json; charset=utf-8",
datatype: "json",
url: URL,
beforeSend: function (XMLHttpRequest) {
//Specifying this header ensures that the results will be returned as JSON.
XMLHttpRequest.setRequestHeader("Accept", "application/json");
},
success: function (data, textStatus, XmlHttpRequest) {
//This function will trigger asynchronously if the Retrieve was successful
var GUID_Retrieved = data.d.results[0].PriceLevelId;
DefaultPriceList(GUID_Retrieved, PriceListName);
},
error: function (XmlHttpRequest, textStatus, errorThrown) {
//This function will trigger asynchronously if the Retrieve returned an error
alert("ajax call failed");
}
});
}
function DefaultPriceList(GUID, NAME){
var lookupValue = new Array();
lookupValue[0] = new Object();
lookupValue[0].id = GUID;
lookupValue[0].name = NAME;
lookupValue[0].entityType = "pricelevel";
Xrm.Page.getAttribute("pricelevelid").setValue(lookupValue);
}
Here are some examples REST queries that demonstrate the type of URL you will need to construct in jscript:
SELECT
| Fields | URL |
| Name | https://gtblog.crm5.dynamics.com/XRMServices/2011/OrganizationData.svc /WorkflowSet ?$select=Name |
| Name, StateCode |
https://gtblog.crm5.dynamics.com/XRMServices/2011/OrganizationData.svc /WorkflowSet ?$select=Name,StateCode |
Note: by default all fields are selected
FILTER
| Fields | URL |
| Name | https://gtblog.crm5.dynamics.com/XRMServices/2011/OrganizationData.svc /WorkflowSet ?$filter=Name%20eq%20'Script X' |
| Name, StateCode |
https://gtblog.crm5.dynamics.com/XRMServices/2011/OrganizationData.svc /WorkflowSet ?$filter=Name%20eq%20'Script X'%20and%20StateCode/Value%20eq%200 Note: The State Code field has multiple attributes so we need to use the syntax “StateCode/Value” to specifically refer to the Value of the StateCode field. |
SELECT and FILTER together
| Fields | URL |
| Name | https://gtblog.crm5.dynamics.com/XRMServices/2011/OrganizationData.svc /WorkflowSet ?$select=Name |
ORDER BY
| Fields | URL |
| Name | https://gtblog.crm5.dynamics.com/XRMServices/2011/OrganizationData.svc /WorkflowSet ?$orderby=Name |
| Name, StateCode |
https://gtblog.crm5.dynamics.com/XRMServices/2011/OrganizationData.svc /WorkflowSet ?$orderby=Name,StateCode |
ORDER BY AND SELECT TOP N
| Fields | URL |
| Name | https://gtblog.crm5.dynamics.com/XRMServices/2011/OrganizationData.svc /WorkflowSet ?$top=1 |
Retrieve the ID of the 1st Active record with Name = ‘X’
| Fields | URL |
| Name, WorkflowID |
https://gtblog.crm5.dynamics.com/XRMServices/2011/OrganizationData.svc /WorkflowSet ?$select=WorkflowId &$filter=Name%20eq%20'Script X'%20and%20StateCode/Value%20eq%200 &$top=1 |
JOINS
| Entities | URL |
| Account, Related Phone Calls |
https://gtblog.crm5.dynamics.com/XRMServices/2011/OrganizationData.svc /AccountSet ?$expand=Account_Phonecalls
|
Note: The expand parameter requires a relationship name. The default REST query response includes a list of all available relationships so make use of that:
STRING COMPARISONS
REFERING TO THE CURRENT USER
/AccountSet?$filter=OwnerId/Id%20eq%20(guid'" + SystemUserId + "')
OPERATORS
Warning: Look out, it’s all case sensitive! Keep referring back to an Internet Explorer to check how case is used, it tends to be different from the schema names used in CRM:
More useful info on this topic here:
http://crmscape.blogspot.com/2011/03/crm-2011-odata-json-and-crm-forms.html
Nice Post Gareth. I just started to work on CRM. I am trying to make filter query on relationship.
AccountSet(Guid’3d8f8114-997d-e011-8641-1cc1def17774′)/contact_customer_accounts?$expand=Contact_ActivityPointers&$filter=ActivityTypeCode%20eq%20′task’
ActivityTypeCode is part of Contact_ActivityPointers. It giving me error and i am unable to apply filter on this level. Can you help me to use filter in relational Query?
Regards
WS
thanks! very nice post.