Pages

Sunday, April 8, 2012

Constructing a Retrieve Query in CRM 2011

CRM 2011 provides the ability to retrieve information from other entities when loading up a CRM form. For the most part, this was achieved using 3rd party add-ons in CRM 4.0. This post will attempt to provide a simple, practical approach for constructing a retrieve query in CRM 2011.

The prerequisites for this are as follows:
 * Alternatively you can download and import a solution containing these 2 web resources from here.

For all forms where you want to employ the use of this retrieve feature, you will need to load the jquery and JSON resources:


Now go ahead and place the following function into one of your form jscript resources:

function retrieveEntityById(entity,entityid,fields,fn) {

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

    $.ajax({
  
        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) {
            fn(data.d);
        },
        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. So for example, if you are on the Quote Product form and you wish to retrieve additional product attributes, you will call the function passing in the following fields:
  • entity - the entity logical name e.g. Product, Contact, Account
  • entityid - the id of the passed in entity
  • fields - you wish returned from the retrieve query. NB: These are case sensitive so be sure to ensure to check how they are defined on the entity. You also can use the OData Query tool to get the syntax. Errors encountered with configuring this are most likely to be in this area.
  • fn - the name of the function you will be using to handle returned results

 function retrieveSample() {

          //Update next 3 lines. NB: Field names in select are case sensitive and must adhere to the schema name
          var entity = "Product";
          var entityid = Xrm.Page.getAttribute("productid").getValue();
          var fields = "?$select=ProductId,DefaultUoMId,ProductNumber";
          
          if (entityid != null) {
            entityData = retrieveEntityById(entity,entityid[0].id,fields,actionFunction);
          }
        }

Finally, you'll need to define the action function to handle the results returned. Below is a sample of how to retrieve the data elements.

 
function actionFunction(entityData) {
                    if (entityData != null) {  
                        Xrm.Page.getAttribute("address1_stateorprovince").setValue(entityData.snt_State);
                        Xrm.Page.getAttribute("address1_city").setValue(entityData.snt_City);
                        Xrm.Page.getAttribute("address1_postalcode").setValue(entityData.snt_name);
                    }    
                }

Now perform whatever jscript manipulation you need to do on the returned results!

3 comments:

  1. That's a pretty intense query, but very useful when using CRM tools.

    ReplyDelete
  2. OMG Thank you!!! the first step to add the jquery and json references are glossed over in every other article I read (or I missed it). Pictures always help :)

    Great blog. Thanks

    ReplyDelete
  3. You're welcome. Glad you like it and thank you.

    ReplyDelete