Sunday, September 27, 2009

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!

9 comments:

  1. Hi Daniel

    First of all thanks for your help and your solution.
    Your solution was working perfectly until i upgraded the crm to rollup 9.
    I think the problem has to do with the code in the file lookupsingle.aspx.
    I haven't any error but also it looks like not working the prefiltering.

    Thanks

    ReplyDelete
  2. Sorry Daniel

    Forget the error.
    The problem was that the rollup change lookupsingle.aspx.
    In the new lookupsingle.aspx re-copy your code all is o.k.

    Sorry again

    ReplyDelete
  3. No problem at all, thanks for sharing with us. I haven't really tried rollup 9, glad to know that you have sorted out.

    ReplyDelete
  4. Just a follow up comment, I did recently update my project to Rollup 9, and didn't experience the problem that was described by the commenter, Mr. Anonymous. ;)

    It's believed that the commenter didn't actually copy looksingle.aspx to ISV folder, instead he did the change to original CRM file, which was overwritten by the rollup update.

    ReplyDelete
  5. Hello all,

    I have a productid lookup in the qoutedetails entity which looks up the product table entity which is a custom entity.

    And again I have a new_professionalsid which holds the names of all the name of all the Professionals in my table.

    I want when I select the Product from the productid entity for it to display the the Product eg) Electrical Engineer.

    The new_professionalsid should lookup a the Product that was chosen and create a filter based on the productid and only bring the names of the indivisuals whose Profession is Electrical Engineer.

    The code:

    OnChange() /* Which I placed in the productid onchange field.*/

    FilterLookup(crmForm.all.productid, crmForm.all.new_professionalsid);

    OnLoad() /* Is placed on the onlad form of the qoutedetail entity */

    FilterLookup = function(source, target)
    {
    if (IsNull(source) || IsNull(target)) { return; }

    var name = IsNull(source.DataValue) ? '' :
    source.DataValue[0].name;

    I dont know where I'm going wrong with my coding please assist

    target.additionalparams = 'search=' + name;

    ReplyDelete
  6. Hello Daniel,

    Using Jim Wang's plug-in approach to filtering the quote detail products look-up, based on the parent Quote's price-list, I am able to see the filtered list of products.

    However, when I use the 'search' icon, located on the look-up window, the product list is no longer filtered by price list.

    Would you know how to filter results returned from the look-up dialog's "search" feature?

    Cheers!

    ReplyDelete
  7. Hello Daniel,
    I have be using the same code for filtering lookup but your approach of copying the lookupsingle.aspx and than adding code is super genius. Thank you for sharing such an idea.

    ReplyDelete
  8. Hi Daniel,
    I'm trying to use your code, but I seem to have an infinite loop in the method window.openStdDlg. It keeps calling itself. I have copied and pasted the code from above. Any idea what could be going wrong.
    Kind regards,
    Dave.

    ReplyDelete
    Replies
    1. @David, that's very strange. You should only include the ReplaceCrmLookup snippet in your form's onload event once. Could it be the case that you have had ReplaceCrmLookup twice in the same form?

      Delete