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;

    $.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.results);
        },
        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; 
 }
} 

CRM/SharePoint integration: Best Practices

In a series of previous posts we reviewed the various settings available for configuring the SharePoint integration with CRM 2011. We started off by reviewing the primary configuration options available. We then attempted to take into consideration the pros and cons of each approach i.e. the entity based approach and the individual path. We then reviewed some SharePoint best practice considerations.

The intent of this post is to bring that all together to arrive at a general purpose recommendation for CRM/SharePoint integration.

As  has hopefully been illustrated in the series of previous posts, there are challenges to either of the SharePoint integration configuration approaches when it comes to having the SharePoint integration take place at multiple CRM levels. In addition to this, it can also been argued that this could also lead to confusion and/or inconsistency in terms of where content is stored. Conversely, we described the benefits of  using SharePoint metadata as part of a SharePoint content management best practice.

Therefore... perhaps we can keep this relatively simple in CRM while leveraging the power of SharePoint to organize the documentation? I'd venture to say that we can achieve this objective by doing as follows:

• Only create the CRM/SharePoint integration at a single level (most likely at the account or contact level).  

• Classify all content stored using SharePoint Content types. For example, we will store a quote for account ABC at the account level but give it a content type of "Quote"; we  will also store a "Statement of Work" for account ABC at the account level but give it a content type of "SOW". And so on and so forth.

By doing so, we achieve the following stated objectives:

• Keep it simple:  In CRM we have only a single location to navigate to for storing and viewing content. This also avoids the "user discretion" problem described.

• Organization: By leveraging SharePoint content types/metadata we are taking advantage of the power of SharePoint for organizing content as described in this post.

Obviously this "best practice" is not a one size fits all and there will always be other things to take into consideration based on specific customer requirements and analysis.

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:

• Download and import the CRM 2011 OData Query Designer into your CRM environment
• Create jscript web resource for the jquery script*
• Create jscript web resource and the json script*

 * 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!

Showing the from, to, cc in Email Associated View

My previous post walked through the process of "IFraming" in an Advanced Find view into a CRM 2011 form (4.0-esque) when the occassion calls for it. In an earlier post, I also reviewed the challenges with trying to embed the actual CRM activities (email, phonecall, appointment, task, fax, etc.) into a parent form. This post will combine the two to show how we can use the embedded Advanced Find approach to work around the shortcomings of the entity-activities relationship.

By way of introduction - one of the issues with the standard "Closed Activities" view is that you are unable to view all the details of the activity in the grid display. The best example are emails which will appear under this view - you are unable to show the "from", "to", "cc" etc. in the Closed Activities view. The reason is quite simple - it is because the "activities" view is a super view that incorporates all the different types of sub-activities beneath it. It is in fact just a pointer to the actual activities (noteworthy: the actual underlying the database view is called activitypointer) and therefore it only includes the columns that are common to all activities.

That explains why it works like this but it doesn't really help us if in fact we wish to display these columns in a grid on the parent contact, account etc. form.

Well, you might ask, why not just sub-grid the underlying activity (let's assume emails) into the form directly using the standard approach. It's a good question and the solution will work... to an extent. The essential issue is that the "Closed Activities" is not related to the parent account in the standard parental-referential relationship. As I've explained previously, the activity is in fact related to the parent account not only via the Regarding attribute but also via the address fields (i.e. from, to, cc, bcc). So the only requirement for an email to rollup to a contact (or any other entity) is that the contact appear on any of the regarding, from, to, cc, bcc lines of the email. In short, the entity-activity relationship is multi-dimensional involving more than just one relationship.

Now going back to our solution above when you directly embed the emails directly on an entity form - well guess what? It's only going to relate to the parent entity via the standard regarding field. So simply put - if you have 5 emails on a contact, 2 of which are related via the "regarding" attribute and the other 3 that are related via one of the other from, to, cc, or bcc - while you will see all 5 emails in the Closed Activities, you will only see 2 of them in the sub-grid you just created. And therein lies the problem.

So in order to get around this we need to - you guessed it - use the embedded Advance Find grid approach. Fortunately, my previous post already walks through the example of embedding this on the contact form (but now we understand the tangible use case of that scenario). You can similarly embed this on the account form - in that case you will need to be sure to construct your initial Advanced Query to handle the rollup scenario (i.e. all contact emails rolling up to an account).

Adding an Advanced Find Query to form

One of the major benefits of CRM 2011 over its predecessor is the ease with which you are able to add sub-grids into a CRM form. This feature has for all intents and purposes replaced the scripting approach to I-Framing in sub-grids that was necessary in 4.0. For the most part that is...

The CRM 2011 feature is very nifty and comes with the following configuration options:

• All Record Types - This is pretty much the equivalent of a "hard-coded" grid in a form. That is, because the grid brings all records that are returned by a particular view it is static and will render the same grid on all open forms for the given entity.
• Only Related Records - This uses the view definition but adds an additional clause that personalizes the view to the record being view (via the referential parent/child relationship in the database). This is generally the configuration that you will use in 90% of cases.

There are however situations where the above configuration options will not suffice. This will be in cases where you want to display a sub-grid on a form via a relationship that is not defined by the default parent/child referential relationship. For example, if the relationship is 2 layers deep (i.e. grandparent/child) - in this case although the records are indirectly related, you won't be able to use the default "Only Related Records" option to make these appear on a sub-grid on a form.

You could of course construct such a view using the Advanced Find view. And if we could then I-Frame this view onto the form, we could also incorporate this more complex relationship via a form grid to cover the small percentage of relationship cases that are not catered for by the out of the box configuration options.

And fortunately we can. The following steps illustrate how this can be achieved. This walkthrough builds on work that others have done.

• Start by building a system view and build the basic filter criteria for the view.

• Using SQL retrieve the fetchXML for the view (you could also download the fetchXML if building this using Advanced Find)

select FetchXml from SavedQuery where Name = 'Email Contact Grid'

• Take the fetchXML and format it appropriately into a jscript function (you can use the example below). Note: you should make it dynamic by replacing the hard-coded filter from the Advanced Find query with a dynamic parameter (highlighted below)

function DisplaySubGrid() {

    var subgrid = document.getElementById("Emails");

    if (subgrid == null) {
        //The subgrid hasn't loaded, wait 1 second and then try again
        setTimeout('DisplaySubGrid()', 1000);
        return;
    } 

    var fetchXml = "<fetch>"

      + " <entity name='email'>"
      + " <attribute name='from' />"
      + " <attribute name='to' />"
      + " <attribute name='subject' />"
      + " <attribute name='modifiedon' />"
      + " <attribute name='activityid' />"
      + " <order attribute='modifiedon' descending='true' />"
      + " <link-entity name='activityparty' from='activityid' to='activityid' alias='aa'>"
      + " <filter type='and'><condition attribute='partyid' operator='eq' uiname='xxx'"
      + " uitype='contact' value='" + Xrm.Page.data.entity.getId() + "' />"
      + " </filter>"
      + " </link-entity>"
      + " </entity>"
      + " </fetch>";

    //Inject the new fetchXml
    subgrid.control.setParameter("fetchXml", fetchXml);
    //Force the subgrid to refresh
    subgrid.control.refresh();
}

• Go back to your system query and clear all the filter criteria you used for obtaining the above and then add filter criteria that will never return any rows (make sure this references an indexed column such as createdon or modifiedon). Also make sure the columns of your view mirror the columns in the FetchXML above. The filter criteria are only dummy as they will be replaced by the dynamic query at run time, but we should be careful to think of potential performance implications assuming the intercept happens after the initial view loads - so be sure it loads no records and is well indexed.

• Go to your form and insert a sub-grid with the "All Record Types" seleted and the Default View referencing the view that you created above. NB: Make sure the subgrid name in the function matches the name of the form subgrid.

•  Place the jscript function into the form and call from the form onload event. Also ensure that
• Now when the form loads, it will pump the query from Advanced Find query into the form grid overriding the definition of that view.

Adding drop down menus to the ribbon

Having briefly discussed the Ribbon Workbench Tool I thought I'd go about providing an example of how to go about adding a drop down menu on the CRM entity form.

Rather than keeping it simple, we'll dive straight into the deep-end and walk through an example of configuring a dynamic drop down menu. Chances are that if you are configuring something like this, you'll need to make it dynamic so it can interact with the elements on your form. This example builds on some other contributions that have been made.

We'll start off with the end result. Say we want to configure the ability to directly call a contact using either the business, mobile or home number present on the contact form. This solution should be dynamic in that it needs to pull the relevant numbers from the form and display them in a drop menu. Should one of the numbers not be present, then the corresponding menu option should not show.

Something like this:

The Ribbon Workbench Tool is a good starting point as it allows you to build the initial framework. We'll skip over that part and go ino the configuration of the ribbon component and the corresponding jscript. We'll do this at a fairly high level - use the screenshot below for reference.

1. In the FlyoutAnchor node make sure to specify the "PopulateDynamically = true" and create a reference to a command definition in the PopulateQueryCommand tag. This function is going to be responsible for creating the menu options.
2. Create the corresponding "Snt.DynamicMenu" Command Definition and reference the "DynamicMenu" jscript function (in an existing web resource jscript file). This simply calls the jscript function and passes in the context variable.
3. The "DynamicMenu" jscript function needs to construct and return the XML for populating the menu options. In this function, you can use all the conditional processing of jscript in order to construct the menu options to suit your implementation.
4. The Command tag in the body of the jscript XML should reference a valid Command Definition back in the RibbonDiffXml section. I'd recommend sticking to the names that have been used (unless you have more than one custom drop  down menu on your form in which case you'll need to distinguish).
5. Create the corresponding "Snt.OptionClicked Command Definition and...
6. reference the "OptionClicked" jscript function. This simply calls the jscript function and passes in the context variable.
7. The "OptionClicked" jscript function is used to handle the mouse click. You can just use the case statement to identify on which menu option the mouse was clicked and perform the necessary processing logic.

That's pretty much it. You can download the files referenced in the screenshots above from here.

Ribbon Workbench Tool

Forget the Visual Ribbon Editor, I have recently come across a more complete solution for configuring the application ribbon in CRM.

Introducing the Ribbon Workbench Tool which can be downloaded from here. This solution appears to support a lot more configuration options than the aforementioned solution. Suffice it to say, I think an image is the best way to encapsulate the myriad configuration options provided by this tool.

An instructional video is available on the product page.

Although I should say that for simple application ribbon modifications, the original Visual Ribbon Editor has proved to be a very simple tool to use so I can certainly still see myself using that option on occasion. But for scenarios involving more complex configuration requirements, such as the need to configure sub-menus (using "FlyoutAnchors") I think this is the tool that I'll be using.

Kudos to the developer.