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

&$filter=Name%20eq%20‘Script X’

 

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

&$orderby=Name

 

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:

image

 

STRING COMPARISONS

image

 

REFERING TO THE CURRENT USER

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

 

OPERATORS

image

 

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:

image

Advertisements

6 thoughts on “Adding Selection Criteria to REST Queries in CRM 2011

  1. Waqar Sohail

    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

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s