Tuesday, February 08, 2011

MSCRM 4.0: Filtered Lookup for "Add Existing..." Button of a CRM N:N View

There has been a question on CRM Development Forum today about whether it's possible to implement a many to many filter lookup functionality.

About one and half years ago, I blogged an approach to implement filtering functionality for lookup fields on CRM form, using George Doubinski's invention. Today, I am going to take this approach one step further, and show you how to apply the same technique to achieve our purpose for a CRM associated view that represents a many-to-many relationship. The CRM form and its associated view could be something similar to the following screenshot.

NN Lookup Filter Form

In the above scrren, I made up a custom entity called Project, which has a N:N relationship with Account entity. My requirement is to only show the account records that have an industry code of "Service Retail" (Again, this is what I came up, your story could be entirely different).

Since I have previously documented how to load CRM associated view in an iframe, I am not going to repeat the same code here.

The following is the procedure that you may follow to implement the aforementioned N:N filtering lookup, which is fairly similar to the filtering functionality for lookup fields on CRM form:

  1. Create a subfolder called CustomLookup under your CRMWeb's ISV folder.
  2. Copy <crmweb folder>\_controls\lookup\lookupsingle.aspx page to your ISV\CustomLookup folder which you have just created, and renamed the file to lookupmulti.aspx.
  3. Add the following script tag to <crmweb folder>\ISV\CustomLookup\lookupmulti.aspx (Please thank George for his code, this is basically a copy from his snippet with one extra line that I added to enable multi-select).   
    <%--
    ********************************** 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");
    
            // Allow multi-select
            crmGrid.MaximumSelectableItems = -1;
    
            // Icing on a cake - ensure that user cannot create new new record from the lookup window
            this._showNewButton = false; 
        } 
    }
    </script> 
    <%--
    ********************************** END: Custom Changes **********************************
    --%>

  4. Use the script in my previous blog post to load the associated view in iframe. The script should be added to your CRM form's onload event.
  5. Then you are ready to add filtering criteria to your many-to-many associated view lookup button. In my case, my requirement is to only show the account records that have an industry code of "Service Retail" (its picklist integer value is 25) as I have just mentioned above.
    getFilteredUrlForAddExistingAccountButton = function(lookupUrl) {
        var iframe = crmForm.all.IFRAME_Accounts; // Change IFRAME_Accounts to your iframe's ID
        var crmGrid = iframe.contentWindow.document.all['crmGrid'];
        
        // If it's not N:N lookup dialog, we skip it. 
        if (lookupUrl.toLowerCase().match(/\/_controls\/lookup\/lookupmulti.aspx/i) == null)
            return lookupUrl;
        
        // If the lookup window is not concerned with the entity that we are interested in, we skip as well
        if (GetQueryString(lookupUrl, 'objecttypes') !== crmGrid.GetParameter('otc'))
            return lookupUrl;
    
        lookupUrl = lookupUrl.replace(/\/_controls\/lookup\/lookupmulti.aspx/i, '/ISV/CustomLookup/lookupmulti.aspx'); 
        
        var filterXml =
    '<fetch mapping="logical">' +
       '<entity name="account">' +
          '<filter>' +
             '<condition attribute="industrycode" operator="eq" value="25" />' + // Industry: Service Retail
          '</filter>' +
       '</entity>' +
    '</fetch>';
    
        // Ensure that search box is not available in the lookup dialog
        lookupUrl = SetQueryString(lookupUrl, 'browse', 1);
        lookupUrl = SetQueryString(lookupUrl, 'ShowNewButton', 0);
        lookupUrl = SetQueryString(lookupUrl, 'search', filterXml);
    
        return lookupUrl;
    };

  6. Add the following immediate JavaScript function to your form's onload event, following the above script
    (function replaceCrmLookups() {
        window.oldOpenStdDlg = window.oldOpenStdDlg || window.openStdDlg;
        window.openStdDlg = function() {
            arguments[0] = getFilteredUrlForAddExistingAccountButton(arguments[0]);
            return oldOpenStdDlg.apply(this, arguments);
        };
    })();

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

    In my case, after I have everything published, my "Add Existing Account" now prompts me the following screen. As you can tell, I intentionally changed the default many-to-may lookup dialog to the single lookup dialog, which is usually considered to be much more user friendly. I believe we have just shot two birds with one stone, isn't that something beautiful?
    CRM Single Lookup Dialog

Note that you should change the iframe ID in the first line of getFilteredUrlForAddExistingAccountButton() function, also ensure that you have constructed filterXml string correctly in the same function.

I hope this helps.

18 comments:

  1. @Anonymous, I will not answer your question, because you simply have no clue how to properly ask questions while reading other people's blog.

    ReplyDelete
  2. Hey Daniel, wonderfull!!! i had teh exact requirement with one of my clients, i used your code.. and it works smoothly.. thank you so much for the code.. it saved me a lot of time...
    Also, i have another requirement for multiple selection on the lookup.. i would try doing it by using teh lookupmulti.aspx instead of lookupsingle.aspx.. please guide if i am in the right direction.. thanks a lot!!

    ReplyDelete
  3. @Teena, you are indeed in the right direction. Actually crmGrid has an option which allows multiple selection when applied, so you may not necessarily need to use lookupmulti.aspx. I don't have an environment with me at this moment, so I cannot give you the name of the parameter, but I believe it is not too hard to find out. Let me know if you run into any challenges when implementing your requirements.

    Cheers,
    Daniel

    ReplyDelete
  4. i am trying using lookupmulti.. but get a blank page.. wondering i am doing teh rest exactly same as i did in teh earlier one.. so should i see for crmGrid parameters instead?

    ReplyDelete
  5. @Teena, I will try to get back to you sometime this evening about the parameter that you can use.

    ReplyDelete
  6. Alright Daniel that will be great, though i see one of them is 'isMultiLine' and its already true.. if it is the right one.. but thank you so much.. looking forward to ur reply.

    ReplyDelete
  7. @Teena, I have added one extra line of code to the original code to allow multi-select. You may want to rename lookupsingle.aspx to lookupmulti.aspx as I have advised in the blog post.

    ReplyDelete
  8. Thank You Daniel, that works!! much thanks to you for this great post!

    ReplyDelete
  9. Thanks Daniel..


    This post really helps me alot.. thanks for the great post..

    ReplyDelete
  10. Great post, Daniel. We've been painfully building bespoke asp pages to enable filtered lookups for some time now so this is brilliant!

    Just one question if you don't mind. We have an instance where we need to work with two associated views on the one form. We have successfully used your approach to display these associated views in IFrames but we are struggling to get the filtered lookup working for both views.

    Can you expand a little on what the immediate java function does and how one might 'duplicate' this to enable two filtered lookups on the one page?

    Many thanks for your time,

    David

    ReplyDelete
  11. Hi David, what the immediate JavaScript function does is to hijack any call to openStdDlg function (a CRM built-in JavaScript function). The function takes a url as its first parameter, the immediate function tries to replace it with a new URL that has filter XML encoded as query string. If you have two iframes in the same form, you will need to change the getFilteredUrlForAddExistingAccountButton function a bit, so that it can work with the two iframe IDs that you have, and also you need to write different filterXml for the two iframes (probably just based on their IDs).

    Hope this helps.

    ReplyDelete
  12. Hi Daniel,

    Many thanks for your reply and insight into how the immediate function works.

    Naively I was trying to get the 2 IFrames working by having two versions of the getFilteredUrlForAddExistingAccountButton function AND two versions of the intermediate function. I'm guessing the second intermediate function definition was the one the system was using so only the second getFilteredUrlForAddExistingAccountButton function was actually called. Since this was for the wrong entity (for at least one of the IFrames) all I got was the usual non-filtered lookup.

    Will go back and try to get the code working using just the one getFilteredUrlForAddExistingAccountButton function.

    Will post how I get on.

    Regards,

    David

    ReplyDelete
  13. Hi Daniel,

    Managed to get it working by using a single function, checking for the entity and setting the iframe, crmGrid and filterXml variables as appropriate.

    Cheers,

    David

    ReplyDelete
  14. HI David, good to know that you have managed to get it working. Thanks for letting me know.

    Cheers,
    Daniel

    ReplyDelete
  15. Dear Daniel,
    I am looking for similar code for CRM 2011.I have same scenario in crm 2011.Can u pls post it over here If u have any code for N:N ,CRM 2011???
    Thanks
    Deepa

    ReplyDelete
  16. @Deepa, I am trying to put together a blog post about how to do it in CRM 2011, stay tuned.

    Cheers,
    Daniel

    ReplyDelete
  17. Hi Daniel,
    Thanks a lot for the post. Sure,it will be helpful for all developers who is new to crm 2011.
    Deepa

    ReplyDelete
  18. Hi Daniel,
    Awsome code, many thanks. Only one tiny issue, after selecting one or more related entities from the lookup the iFrame does not automatically refresh. The related form does refresh from the entities menu but not the iFrame. I have checked that I have copied your code correctly. Any thoughts?
    Cheers,
    Jules

    ReplyDelete