MS CRM 4.0 Filtered Lookups

I just finished implementing a number of filtered lookups with Michael Höhne's Stunnware Filtered Lookup 4.0. Like most developers I have to think seriously about whether I want to build something vs. pay for someone else's work.

There has been a lot of work put into this product since its introduction with CRM 3.0. The 4.0 release seems pretty well sorted and really makes creating filtered lookups an easy task to accomplish. Supporting the Outlook client, multiple languages, multi-tenancy, and IFD makes this a much better solution then most(probably all) of us would take the time to build on our own.

Filtered Lookups have been reinvented far too many times and become maintenance issues for many upgrading from CRM 3.0 to CRM 4.0. I would rather not have to dig through another guy's failed attempt at this functionality in the future. I personally believe MS should pull this functionality into its product, but barring that, I think using a product like this is the best way to go.

I'm currently using it for a growing list of filtered Lookups for one my customers.  This includes some pretty standard lookups like the Account: Primary Contact Lookup, the Case: Responsible Contact, and the Email To:, CC:, and BCC: fields, as well as some for custom fields.

What is typically involved in making a filtered lookup with this tool is the following:

1. Settings -> Filtered Lookup: Create a Retrieve Multiple Queries entity
   • This may involve using the Fetch Wizard to generate the query.
2. Settings -> Filtered Lookup: Create a Single-View Lookup entity
3. Settings -> Customizations: Add JavaScript to your entity's OnLoad event.

Here is a simple example:

I needed a filtered list for Reseller Accounts on an opportunity.

So I created a new Retrieve Multiple Query entity,

copied the Query from the Active Accounts example and added a filter for an industrycode picklist value of 21,

created a Single-View Lookup using the Query above,

setup the columns for that Lookup, 

set the Labels to English,

added this JavaScript to my Opportunity's OnLoad Event. Published the opportunity and I was done.

SW_IS_LICENSED_USER = false;

try 
{
    var httpRequest = new ActiveXObject("Msxml2.XMLHTTP");
    httpRequest.open("GET", prependOrgName("/isv/stunnware.com/cld4/cld4.aspx?orgname=" + ORG_UNIQUE_NAME), false);
    httpRequest.send(null);
    eval(httpRequest.responseText);
}

catch(e) {
}

if (SW_IS_LICENSED_USER) 
{
    var resellerAccountLookup = new SwSingleLookup("new_resellerid");

    if (resellerAccountLookup.existsOnForm()) 
    {
         resellerAccountLookup.setLookupClass("ResellerAccounts");
     }
}

All in all that took maybe 10 minutes including testing.

That was a very simple case. The examples in the help file show you how to create the Account: Primary Contact filtered lookup showing only Contacts associated with that Account. Implementing this only required cutting from the example and pasting into the OnLoad Event of the account form. Another example gave me the information I needed to to implement the Case Responsible Contacts Filter where the Filtered Lookup changes when the case's account is changed. This required a little JavaScript added to the OnChange for the customerid as well as the Onload for the form.

The email modifications I made were also very easy to implement as shown in the code that follows. Since the Account is almost always preloaded in the To: field, it was easy to overload the lookups for the target selections to default to the Contacts related to that Account.

SW_IS_LICENSED_USER = false;

try {
    var httpRequest = new ActiveXObject("Msxml2.XMLHTTP");
    httpRequest.open("GET", prependOrgName("/isv/stunnware.com/cld4/cld4.aspx?orgname=" + ORG_UNIQUE_NAME), false);
    httpRequest.send(null);
    eval(httpRequest.responseText);
}

catch(e) {
}

if (SW_IS_LICENSED_USER) 
{  
   if ( crmForm.all.to.DataValue != null )
   {
     var accountid =  crmForm.all.to.DataValue[0].id;
    
     var toContactLookup = new SwSingleLookup("to");
     toContactLookup.setParameter("parentcustomerid", accountid);
     toContactLookup.setLookupClass("AssociatedContacts");

     var ccContactLookup = new SwSingleLookup("cc");
     ccContactLookup.setParameter("parentcustomerid", accountid);
     ccContactLookup.setLookupClass("AssociatedContacts");

    var bccContactLookup = new SwSingleLookup("bcc");
    bccContactLookup.setParameter("parentcustomerid", accountid);
    bccContactLookup.setLookupClass("AssociatedContacts");
   }

}

For this instance I am still relying on the Form Assistant if the end user wants to select multiple Contacts for a single field or add a CRM user, which is a rare case for this customer. The Form Assistant is not modified by these lookups in any way.

Understanding 3 Important Lines of Code
There are three important lines of code to that you will use to create a filtered lookup.

1. Create a new lookup to override the existing lookup for specified field on the form. In this case the "to" field on the email form. 

      var toContactLookup = new SwSingleLookup("to");

2. Set a filter parameter that is defined in your Fetch and assign a value. 

      toContactLookup.setParameter("parentcustomerid", accountid);

Below is the Fetch that you can access under settings. Notice the parentcustomerid field in the filter section. You are not limited to a single parameter if you need additional criteria for your filter.

<fetch mapping="logical">
   <entity name="contact">
      <attribute name="emailaddress1" />
      <attribute name="telephone1" />
      <attribute name="fullname" />
      <order attribute="fullname" />
      <filter>
         <condition attribute="statecode" operator="eq" value="0" />
         <condition attribute="parentcustomerid" operator="eq" param="parentcustomerid" />
      </filter>
   </entity>
</fetch>

3. Set the class that you are using.  

     toContactLookup.setLookupClass("AssociatedContacts");

That's it, just save your customization and publish. You now have a working filtered Lookup.

Just the Beginning 
These are some very simple examples, but it is obvious how easily much more complex filters could be. If you are going to create complex Retrieve queries, it is worth downloading the Fetch Wizard as well, which is a really easy way to create the XML needed for the fetches that this tool relies on. The Fetch Wizard is part of the Stunnware Tools 4.0. that are available for download at no charge.

What you will see Under Settings-> Filtered Lookup are the following which will allow you to configure everything but the little bit of JavaScript that you will add to your forms, and there are even tools for tracing your filtered lookups if something isn't working quite right.

This tool is made for a developer to use, but a person comfortable with customizing CRM and with some JavaScript experience should be able to get by. It requires an installation process that can require you to manually update the XML of your ISV.config and SiteMap, but if you read the instructions, it will all make sense.

If you are playing with the MS CRM 4.0 VPC Image, there is a license for that image that you can download at no cost to test this system out.

If you are considering reinventing this wheel, I seriously recommend trying this solution out first.