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.