MSCRM 4.0 Filtered Lookup

Filtered lookup is a frequently requested features in CRM projects, however there is no out-of-box solution from CRM 4 platform itself.

George Doubinski previously provided a solution for filtered lookup in CRM4, but it requires you to modify CRM application files, which I usually try to avoid unless absolutely no choice, as it will cause deployment and maintenance issue.

Jim Wang also provided a plug-in based solution (his example is basically for N:N relationship, but the concept pretty much applies to typical 1:N lookup as well), which is a good solution if you want an application-wide filtering lookup for a particular entity. By application-wide, I mean you are using the same filtering criteria everywhere in your application. If we put it the other way, you don't have the flexibility of using different filtering lookup for different forms when the plug-in based approach is used. One more issue to be aware is that the plug-in approach is basically hijacking all Execute messages, which could introduce some performance overhead to CRM platform, as Execute message is such a popular message on CRM platform.

After tweaking around George's solution, I have coined a slightly better solution based on George's approach. The following are the steps that I have taken.

1. Create a subfolder called CustomLookup under your CRM's ISV folder.
2. Copy <crmweb folder>\_controls\lookup\lookupsingle.aspx page to your ISV\CustomLookup folder which you have just created.
3. Add the following script tag to <crmweb folder>\ISV\CustomLookup\lookupsingle.aspx (Please thank George for his code, this is basically a copy from his blog).   
   

   <%--
   ********************************** BEGIN: Custom Changes **********************************
   This page is a copy of <crmweb folder>\_controls\lookup\lookupsingle.aspx file 
   with the following custom change. 
   --%>
   <script runat="server"> 
   protected override void OnLoad( EventArgs e ) 
   { 
         base.OnLoad(e); 
         crmGrid.PreRender += new EventHandler( crmgrid_PreRender ); 
   } 
   
   void crmgrid_PreRender( object sender , EventArgs e ) 
   {
       // As we don't want to break any other lookups, ensure that we use workaround only if
       // search parameter set to fetch xml.
       if (crmGrid.Parameters["search"] != null && crmGrid.Parameters["search"].StartsWith("<fetch")) 
       { 
           crmGrid.Parameters.Add("fetchxml", crmGrid.Parameters["search"]);  
   
           // searchvalue needs to be removed as it's typically set to a wildcard '*' 
           crmGrid.Parameters.Remove("searchvalue");  
   
           // Icing on a cake - ensure that user cannot create new new record from the lookup window
           this._showNewButton = false; 
       } 
   }
   </script> 
   <%--
   ********************************** END: Custom Changes **********************************
   --%>

4. Add the following script to your form’s onload event
   

   (function ReplaceCrmLookup() {
       window.oldOpenStdDlg = window.openStdDlg;
       window.openStdDlg = function() {
           arguments[0] = arguments[0].replace(/\/_controls\/lookup\/lookupsingle.aspx/i, '/ISV/CustomLookup/lookupsingle.aspx');
           return oldOpenStdDlg.apply(this, arguments);
       };
   })();

   
   
5. Then you are ready to add filtering criteria to your lookup field. For instance, there is a business requirement that you need to do filtering for the Primary Contact field of Account form, so that when the Primary Contact lookup is clicked, the lookup window will only show the contact records that are associated to the current account. Your code will be like this:
   

   (function AddFilterToPrimaryContactEntity() {
       var lookup = crmForm.all.primarycontactid; 
       if(crmForm.ObjectId == null) { 
           // Disable lookup for new account record as there can be no contacts 
           field.Disabled = true; 
       } 
       else { 
           // Ensure that search box is not visible in a lookup dialog
           lookup.lookupbrowse = 1; 
           
           // Pass fetch xml through search value parameter 
           lookup.AddParam('search', 
   '<fetch mapping="logical">' +
      '<entity name="contact">' +   
         '<filter>' +
            '<condition attribute="parentcustomerid" operator="eq" value="' + crmForm.ObjectId + '" />' +
         '</filter>' + 
      '</entity>' +
   '</fetch>'); 
       }
   })();

   
   
6. Save your form and publish your entity, you are good to go now.

Basically this approach opens up a lot of possibilities when you want to replace a CRM built-in page with your own ones. You can add similar filtering functions to a N:N lookup when "Add Existing ..." button is clicked ([UPDATE - Feb 8, 2011] I implemented this function since there was a request for this feature in CRM Development Forum).

Note that when you implement a filtered lookup field using the script here, you need to check "Turn off automatic resolutions in field" option in the CRM lookup field's property window, otherwise the lookup dialog page will throw an error when the user has typed in something in the lookup textbox. The option is unchecked by default.

Will this make it a supported solution? Not really. But your future rollup update should at least not overwrite your code.

You may wonder why we should use this approach instead of George's organic one? The reason is, using this technique, you don't mess with CRM system files, there are a couple of benefits associated with this:

1. Your modified CRM system file (lookupsingle.aspx in our case) is stored in the ISV folder, your code will have much better chance to survive when you apply new Rollup Update to your CRM server. ISV folder is simply your empire, Microsoft Dynamics CRM server rollup update is supposed to never touch the files in the ISV folder. Lets's put it the other way, due to the fact that CRM Rollup is always a cumulative update package that includes all the fixes since the RTM version (the very first version of CRM 4.0), it could update and overwrite any files that have ever been changed since Rollup 1, which means your modified lookupsingle.aspx file could be overwritten by every single new Rollup Update you are planning to apply to your CRM server, if the organic approach is used. 
2. The modified CRM system file is isolated in your ISV folder, it will make thing a lot easier to maintain the application. Also it will be easier to create your own installation package for your custom application. 

Credit goes to George Doubinski. Happy CRM'ing!