Filtered Lookups in CRM 2011

Often when working with CRM Projects we can see requirements where we require a selection of other CRM records filtered to only records that are associated with an earlier user selection.  A common example would be selecting a key contact or finance contact for an Opportunity or Order, but other examples of this could often be seen across different project requirements – say for selecting a Property from within a particular Portfolio, or selecting a particular Order to Invoice that related to a specific Customer Account.

In earlier posts on this blog I have looked at different approaches that could be used to implement a filtered lookup for CRM 4, this article aims to show how we can implement simpler solutions using the new functionality in CRM 2011.

Earlier Posts concerning Filtered Lookups within CRM 4

Filtered Lookup as a Drop-down Picklist in MSCRM

Unsupported Method for Filtered Lookup in MSCRM 4.0

Initially this post will look at the simpler and more best practise method for implementing a Filtered Lookup through the now standard CRM 2011 customisation options, and then take a look at a more complex Form Scripting method of achieving the same result.

Filtered Lookups via Simple Customisation in CRM 2011

When adding a Lookup field to a Form in CRM 3 or 4 we were restricted to a specific ‘Lookup View’ that defined how records of that particular entity would appear within the lookup – this was quite restrictive and forced us to have a fixed set of fields for all Lookups to a certain entity type across a MSCRM Solution.

CRM 2011 attempts to resolve this problem by offering us options to set both:

Configuring the properties for a Lookup control in CRM 2011

Selecting which view to use to display possible Lookup selections, as well as controlling which Views the CRM User is allowed to select from.

Selecting a filter to show only records that match a certain dynamic criteria, with the option to control whether the User is allowed to remove the Filter or not.

This allows us to control how the Lookup will appear in CRM 2011 through simple customisation, and so avoid developing any code to produce a Filtered Lookup.

So in the example above – we would facilitate a Lookup which would display the same fields as the ‘Active Contacts’ view, and crucially, would only display Contacts where:

[CONTACT].[(Account) PARENT CUSTOMER] = [OPPORTUNITY].[(Account) POTENTIAL CUSTOMER]

Or in real-world terms, where the Account associated to a Contact is the same as the Account associated to the Opportunity. 

(on the basis that the Contact’s Parent Customer relationship is with an Account record, and similarly the Opportunity’s Potential Customer relationship is to an Account record.)

With the result being a useful filtered Lookup which allows the CRM User to choose a particular Point of Contact for the Opportunity from the established list of Contacts associated to the Account:

Selecting a Contact for a particular Account via the Filtered Lookup

Complex Filtered Lookups via Form Scripting in CRM 2011

The example shown above gives the best supported method for adding a Filtered Lookup and is considerable easier than attempting to implement a similar Lookup in CRM 4, however in some instances we may require a more complex Filtering than the Related Records Filtering allows.

To do this, we can look at incorporating 2011 Form Scripting via a Web Resource to invoke methods in the CRM 2011 Form Document Object Model to manipulate the behaviour of the Lookup fields on the Form.

Behind the CRM 2011 Forms, the Document Object Model has been significantly changed from CRM 4 to support a series of Xrm methods to both give CRM Developers further control over the Form and Controls through well-defined methods as opposed to direct modifications to the DOM as was the case in CRM 3 and 4.  This then allows for a move towards full support for HTML 5 and therefore multi-browser support. (which is widely expected to be built into the future releases of CRM according to the recent May 2011 Statement of Direction)

In this vein, these Xrm methods provide us powerful methods for manipulating Lookup Controls to provide an additional method of implementing a filtered lookup.

addCustomView	Adds an entirely new custom view to the Lookup Control.
setDefaultView	Sets the initial default view when a user clicks on the Lookup Control.
getDefaultView	Returns the GUID for the initial view when a user clicks on the Lookup Control.

(further detail on the Xrm.Page methods can be found at the following page within the CRM 2011 SDK.)

We can use these methods to build ourselves a simple set of scripting to build and add a custom view to the ‘Point of Contact’ field that will provide the Filtered Lookup:

var defaultViewId;

// FUNCTION: formOnLoad
function formOnLoad(accountFieldName, lookupFieldName) {

  defaultViewId = Xrm.Page.getControl(lookupFieldName).getDefaultView();
  setLookup(accountFieldName, lookupFieldName, false);

}

// FUNCTION: setLookup
function setLookup(accountFieldName, lookupFieldName, resetSelection) {

    // Get the selected Account Id in the [accountFieldName] indicated control
    var account = Xrm.Page.getAttribute(accountFieldName).getValue();

    if ( account != null )
    {
      var accountid = account[0].id;
      var accountname = account[0].name;

      if ( resetSelection == true )
      {
        // reset old selection for Contact
        Xrm.Page.getAttribute(lookupFieldName).setValue(null);
      }

      // use randomly generated GUID Id for our new view
      var viewId = "{1DFB2B35-B07C-44D1-868D-258DEEAB88E2}";
      var entityName = "contact";

      // give the custom view a name
      var viewDisplayName = "Active Contacts for " + accountname + "";

      // find all contacts where [Parent Customer] = [Account indicated by AccountId]
      // AND where [Statecode] = Active
      var fetchXml = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>" +
                     "<entity name='contact'>" +
                     "<attribute name='firstname' />" +
                     "<attribute name='lastname' />" +
                     "<attribute name='contactid' />" +
                     "<order attribute='lastname' descending='false' />" +
                     "<filter type='and'>" +
                     "<condition attribute='parentcustomerid' operator='eq' value='" + accountid + "' />" +
                     "<condition attribute='statecode' operator='eq' value='0' />" +
                     "</filter>" +
                     "</entity>" +
                     "</fetch>";

      // build Grid Layout
      var layoutXml = "<grid name='resultset' " +
                               "object='1' " +
                               "jump='contactid' " +
                               "select='1' " +
                               "icon='1' " +
                               "preview='1'>" +
                           "<row name='result' " +
                                "id='contactid'>" +
                             "<cell name='firstname' " +
                                   "width='200' />" +
                             "<cell name='lastname' " +
                                   "width='250' />" +
                             "<cell name='parentcustomerid' " +
                                   "width='250' />" +
                           "</row>" +
                         "</grid>";

      // add the Custom View to the indicated [lookupFieldName] Control
      Xrm.Page.getControl(lookupFieldName).addCustomView(viewId, entityName, viewDisplayName, fetchXml, layoutXml, true);
    }
    else
    {
      // no Account selected, reset Contact Lookup View to the default view such that all Contacts are displayed for selection
      Xrm.Page.getControl(lookupFieldName).setDefaultView(defaultViewId);
    }
}

With this scripting in place via the Web Resource, we can connect the Form and Field events to invoke the script functions:

Attaching the ‘setLookup’ event from the Scripting contained in the Web Resource to the Opportunity’s ‘customerid’ field onChange Event

Attaching the ‘formOnLoad’ event from the Scripting contained in the Web Resource to the Opportunity’s onLoad Event

Once published, we can open an Opportunity and select a Potential Customer Account to then automatically limit the ‘Point of Contact’ field to just the Active Contacts that are linked to the selected Account:

Selecting an Account record for the Opportunity’s Potential Customer

This then acts to limit the user’s selection of a Point of Contact to the relevant Contacts for the Opportunity:

Selecting a Contact for a particular Account via the Filtered Custom View supplied by Form Scripting”

In determining how best to cater for these Scripted Filtered Lookups in CRM 2011 I found the following articles and supporting Javascript very useful:

http://www.harris-tech.com/blogs/bid/54720/Creating-Filtered-Lookups-in-CRM-2011

http://nishantrana.wordpress.com/2010/12/31/filtered-lookup-in-crm-2011/

The Managed and Unmanaged Solution XML for this scripted ‘Point of Contact’ customisation to CRM can be downloaded via the following links:

http://cid-0f98a78f1c3c4457.office.live.com/embedicon.aspx/.Public/PointOfContact_0_0_1_0_Managed.zip

http://cid-0f98a78f1c3c4457.office.live.com/embedicon.aspx/.Public/PointOfContact_0_0_1_0.zip

From these steps hopefully this gives a good idea of how Filtered Lookups can be implemented in CRM 2011 in most cases without recourse to development, and how when development is required that this can be significantly reduced and better managed than in earlier versions of CRM – with the scripting required here a good introduction to how the Xrm methods change how we can use Form Scripting in Dynamics CRM.

The Form Scripting model of CRM 2011 is something I would like to return to in different posts, particularly the changes here from CRM 4 and the ability via Web Resources to incorporate Javascript libraries such as jQuery into Dynamics CRM Solutions to give us more flexibility in scripting as opposed to requiring more traditional server-side .NET coding.