Upgrading our internal CRM 4 system to CRM 2011 has given me a great opportunity to get used to the new object model in CRM 2011.  I have to admit that I REALLY like the new object model a lot!  The new object model makes working with the controls and data on a CRM form easier than ever before.

One of the things that always frustrated me in CRM 4 was the inability to create filtered lookups.  Sure…a developer could hack the system to try to make a filtered lookup or use 3^rd party products to achieve this functionality, but this was a piece of functionality that was sorely missing from the product…until now.

So what is a filtered lookup?  A filtered lookup is a lookup whose results are filtered based on a value that is stored in another field, most likely another lookup on the same form.  Imagine an example where a form in CRM had both an account and contact lookup on it.  In CRM 4, the user would select an account in the account lookup but when they tried to select a contact in the contact lookup, the list of contacts were all the contacts in the database...not just those contacts at the selected account.  In CRM 2011 we can now add a little code that DOES allow the contact lookup to be “filtered” based on what was selected in the account lookup.

Below is a picture from our CRM 4 system for a work ticket:

In the image, the Account field is set to Harris Technology.  When I click on the lookup for Project, I get the following from CRM:

As you can see here, the lookup is not filtered in any way.   CRM displays all of the projects in my database regardless of whether or not the project belonged to this account or whether or not it was complete.  In an ideal world, I would have wanted the lookup to only show projects that had not been completed for Harris Technology only.

In CRM 2011, I can use a new method in the Xrm.Page.ui object called addCustomView.  Using this method I can add a custom view to my project lookup that is filtered to projects linked to Harris Technology and that have not been marked complete.  The syntax for this new method is shown below:

controlObj.addCustomView(viewId, entityName, viewDisplayName, fetchXml, layoutXml, isDefault)

To create my filtered lookup, I have to pass the method the following:

·         viewId – This is just a unique guid for the view.  You can use a variety of tools out there to generate this id.

·         entityName – This is the name of the base entity we are performing a lookup on.

·         viewDisplayName – This is what our custom view name will be called in our lookup.

·         fetchXml – FetchXML is the query language that CRM uses to query the database.  To get this code easily, simply build an Advanced Find in CRM 2011 and choose the Download FetchXML button in the ribbon.

·         layoutXml – This XML defines what the grid will look like when the user uses your custom lookup.

·         isDefault – If set to TRUE, the CRM system will default the lookup to the custom lookup defined by your code.

Below is the function I created that creates the filtered lookup in CRM 2011.

function SetProjectLookup() {

    //get the current account

    var account = Xrm.Page.getAttribute("ht_accountid").getValue();

    var accountid = account[0].id;

    var accountname = account[0].name;

    //build fetchxml

    var viewId = "{65EC9B45-EE81-4F89-BAF6-E8603FF8E1E4}";

    var entityName = "ht_project";

    var viewDisplayName = "Active Projects for " + accountname;

    var fetchXml = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>" +

                   "<entity name='ht_project'>" +

                   "<attribute name='ht_name' />" +

                   "<attribute name='ht_projectstatus' />" +

                   "<attribute name='ht_projectid' />" +

                   "<order attribute='ht_name' descending='false' />" +

                   "<filter type='and'>" +

                   "<condition attribute='ht_accountid' operator='eq' value='" + accountid + "' />" +

                   "<condition attribute='ht_projectstatus' operator='ne' value='1' />" +

                   "</filter>" +

                   "</entity>" +

                   "</fetch>";

    //build grid layout

    var layoutXml = "<grid name='resultset' " +

                             "object='1' " +

                             "jump='ht_projectid' " +

                             "select='1' " +

                             "icon='1' " +

                             "preview='1'>" +

                         "<row name='result' " +

                              "id='ht_projectid'>" +

                           "<cell name='ht_name' " +

                                 "width='300' />" +

                           "<cell name='ht_projectstatus' " +

                                 "width='100' />" +

                                 "disableSorting='1' />" +

                         "</row>" +

                       "</grid>";

    //add new view view

    Xrm.Page.getControl("ht_projectid").addCustomView(viewId, entityName, viewDisplayName, fetchXml, layoutXml, true);

}

I execute this code in the OnChange event on the Account field.  Now when the user selects the project lookup, they see:

This is a HUGE improvement over the lookups in CRM 4!  I did not need to use a 3^rd party tool to achieve this result nor did I have to code something that would not be supported by Microsoft.  This new filtered lookup functionality should greatly enhance our ability to deliver customizations that our customers desire with exactly the functionality they expect in their CRM systems.

Until next time...Happy Coding!!!