Thursday, April 19, 2012

Constructing a Retrieve Multiple Query in CRM 2011

We looked at how to construct a Retrieve Query in CRM. That example, addressed the "singleton" case i.e. retrieve a single record from an entity by passing in the entity ID. This post will walk through how to construct and process a retrieve multiple query in CRM. The pre-requisites for this to work are the same as those listed in my previous post.

First of all go ahead and place the following function into one of your form jscript references:

function retrieveMultiple(entity,filter,fields,fn) {

    var context = Xrm.Page.context;
 var serverUrl = context.getServerUrl();
 var oDataSelect;
    // build query string
 oDataSelect = "/XRMServices/2011/OrganizationData.svc/" + entity + "Set"+fields+"&$filter="+filter;

        type: "GET",
        contentType: "application/json; charset=utf-8",
        datatype: "json",
        url: serverUrl + oDataSelect,
        beforeSend: function (XMLHttpRequest) { XMLHttpRequest.setRequestHeader("Accept", "application/json"); },
        success: function (data, textStatus, XmlHttpRequest) {
        error: function (xmlHttpRequest, textStatus, errorThrown) {
            alert("Status: " + textStatus + "; ErrorThrown: " + errorThrown);

The next step is to call the above function to retrieve the values you are looking for. You will need to call the function passing in the following parameters:

  • entity - the entity logical name e.g. Product, Contact, Account
  • filter - a valid filter clause which will retrieve 1 or more records from the entity (see example)
  • fields - the list of fields in the select statement you wish retrieve from the query. NB: These are case sensitive so be sure to check how they are defined on the entity. You can also use the OData Query tool to get the syntax. Errors encountered with configuring this option are most likely to be with the case sensitivity of the fields
  • fn - the name of the function you will be using to handle the query results

The following is a sample call to the retrieveMultiple function:

 function retrieveSample() {

 //Update next 5 lines. NB: Field names in select are case sensitive and must adhere to the schema name
 var entity = "new_contact_systemuser";
 var contactid = Xrm.Page.getAttribute("contactid").getValue();
 var systemuserid = Xrm.Page.context.getUserId();
 var filter = "contactid eq guid'" + contactid[0].id + "'" + " and "+  "systemuserid eq guid'" + systemuserid[0].id + "'";
 var fields = "?$select=systemuserid";
 if (entityid != null) {
  entityData = retrieveMultiple(entity,filter,fields,actionFunction);

Note the following:

  • The filter is a compound where-clause. You can construct this in the same way you would construct a regular SQL query.
  • We are only retrieving a single field in this query. Multiple fields could be retrieved by adding onto the select-clause as you would in a regular SQL query.

Finally you will need to define the action function to handle the returned results. Below is a sample of how to go about processing the results:

function actionFunction(entityData) {
 for( i=0; i< entityData.length; i++)  {     
  var entity = entityData[i];     
  var id = entity.systemuserid; 

No comments:

Post a Comment