Filtered Lookups in CRM 2011

Often when working with CRM Projects we can see requirements where we require a selection of other CRM records filtered to only records that are associated with an earlier user selection.  A common example would be selecting a key contact or finance contact for an Opportunity or Order, but other examples of this could often be seen across different project requirements – say for selecting a Property from within a particular Portfolio, or selecting a particular Order to Invoice that related to a specific Customer Account.

In earlier posts on this blog I have looked at different approaches that could be used to implement a filtered lookup for CRM 4, this article aims to show how we can implement simpler solutions using the new functionality in CRM 2011.

Earlier Posts concerning Filtered Lookups within CRM 4
Filtered Lookup as a Drop-down Picklist in MSCRM
Unsupported Method for Filtered Lookup in MSCRM 4.0

Initially this post will look at the simpler and more best practise method for implementing a Filtered Lookup through the now standard CRM 2011 customisation options, and then take a look at a more complex Form Scripting method of achieving the same result.

Filtered Lookups via Simple Customisation in CRM 2011

When adding a Lookup field to a Form in CRM 3 or 4 we were restricted to a specific ‘Lookup View’ that defined how records of that particular entity would appear within the lookup – this was quite restrictive and forced us to have a fixed set of fields for all Lookups to a certain entity type across a MSCRM Solution.

CRM 2011 attempts to resolve this problem by offering us options to set both:

image

Configuring the properties for a Lookup control in CRM 2011

  • Selecting which view to use to display possible Lookup selections, as well as controlling which Views the CRM User is allowed to select from.
  • Selecting a filter to show only records that match a certain dynamic criteria, with the option to control whether the User is allowed to remove the Filter or not.
  • This allows us to control how the Lookup will appear in CRM 2011 through simple customisation, and so avoid developing any code to produce a Filtered Lookup.

    So in the example above – we would facilitate a Lookup which would display the same fields as the ‘Active Contacts’ view, and crucially, would only display Contacts where:

    [CONTACT].[(Account) PARENT CUSTOMER] = [OPPORTUNITY].[(Account) POTENTIAL CUSTOMER]

    Or in real-world terms, where the Account associated to a Contact is the same as the Account associated to the Opportunity. 

    (on the basis that the Contact’s Parent Customer relationship is with an Account record, and similarly the Opportunity’s Potential Customer relationship is to an Account record.)

    With the result being a useful filtered Lookup which allows the CRM User to choose a particular Point of Contact for the Opportunity from the established list of Contacts associated to the Account:

    image

    Selecting a Contact for a particular Account via the Filtered Lookup

    Complex Filtered Lookups via Form Scripting in CRM 2011

    The example shown above gives the best supported method for adding a Filtered Lookup and is considerable easier than attempting to implement a similar Lookup in CRM 4, however in some instances we may require a more complex Filtering than the Related Records Filtering allows.

    To do this, we can look at incorporating 2011 Form Scripting via a Web Resource to invoke methods in the CRM 2011 Form Document Object Model to manipulate the behaviour of the Lookup fields on the Form.

    Behind the CRM 2011 Forms, the Document Object Model has been significantly changed from CRM 4 to support a series of Xrm methods to both give CRM Developers further control over the Form and Controls through well-defined methods as opposed to direct modifications to the DOM as was the case in CRM 3 and 4.  This then allows for a move towards full support for HTML 5 and therefore multi-browser support. (which is widely expected to be built into the future releases of CRM according to the recent May 2011 Statement of Direction)

    In this vein, these Xrm methods provide us powerful methods for manipulating Lookup Controls to provide an additional method of implementing a filtered lookup.

    addCustomView Adds an entirely new custom view to the Lookup Control.
    setDefaultView Sets the initial default view when a user clicks on the Lookup Control.
    getDefaultView Returns the GUID for the initial view when a user clicks on the Lookup Control.

    (further detail on the Xrm.Page methods can be found at the following page within the CRM 2011 SDK.)

    We can use these methods to build ourselves a simple set of scripting to build and add a custom view to the ‘Point of Contact’ field that will provide the Filtered Lookup:

    var defaultViewId;
    
    // FUNCTION: formOnLoad
    function formOnLoad(accountFieldName, lookupFieldName) {
    
      defaultViewId = Xrm.Page.getControl(lookupFieldName).getDefaultView();
      setLookup(accountFieldName, lookupFieldName, false);
    
    }
    
    // FUNCTION: setLookup
    function setLookup(accountFieldName, lookupFieldName, resetSelection) {
    
        // Get the selected Account Id in the [accountFieldName] indicated control
        var account = Xrm.Page.getAttribute(accountFieldName).getValue();
    
        if ( account != null )
        {
          var accountid = account[0].id;
          var accountname = account[0].name;
    
          if ( resetSelection == true )
          {
            // reset old selection for Contact
            Xrm.Page.getAttribute(lookupFieldName).setValue(null);
          }
    
          // use randomly generated GUID Id for our new view
          var viewId = "{1DFB2B35-B07C-44D1-868D-258DEEAB88E2}";
          var entityName = "contact";
    
          // give the custom view a name
          var viewDisplayName = "Active Contacts for " + accountname + "";
    
          // find all contacts where [Parent Customer] = [Account indicated by AccountId]
          // AND where [Statecode] = Active
          var fetchXml = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>" +
                         "<entity name='contact'>" +
                         "<attribute name='firstname' />" +
                         "<attribute name='lastname' />" +
                         "<attribute name='contactid' />" +
                         "<order attribute='lastname' descending='false' />" +
                         "<filter type='and'>" +
                         "<condition attribute='parentcustomerid' operator='eq' value='" + accountid + "' />" +
                         "<condition attribute='statecode' operator='eq' value='0' />" +
                         "</filter>" +
                         "</entity>" +
                         "</fetch>";
    
          // build Grid Layout
          var layoutXml = "<grid name='resultset' " +
                                   "object='1' " +
                                   "jump='contactid' " +
                                   "select='1' " +
                                   "icon='1' " +
                                   "preview='1'>" +
                               "<row name='result' " +
                                    "id='contactid'>" +
                                 "<cell name='firstname' " +
                                       "width='200' />" +
                                 "<cell name='lastname' " +
                                       "width='250' />" +
                                 "<cell name='parentcustomerid' " +
                                       "width='250' />" +
                               "</row>" +
                             "</grid>";
    
          // add the Custom View to the indicated [lookupFieldName] Control
          Xrm.Page.getControl(lookupFieldName).addCustomView(viewId, entityName, viewDisplayName, fetchXml, layoutXml, true);
        }
        else
        {
          // no Account selected, reset Contact Lookup View to the default view such that all Contacts are displayed for selection
          Xrm.Page.getControl(lookupFieldName).setDefaultView(defaultViewId);
        }
    }
    

    With this scripting in place via the Web Resource, we can connect the Form and Field events to invoke the script functions:

    image

    Attaching the ‘setLookup’ event from the Scripting contained in the Web Resource to the Opportunity’s ‘customerid’ field onChange Event

    image

    Attaching the ‘formOnLoad’ event from the Scripting contained in the Web Resource to the Opportunity’s onLoad Event

    Once published, we can open an Opportunity and select a Potential Customer Account to then automatically limit the ‘Point of Contact’ field to just the Active Contacts that are linked to the selected Account:

    image

    Selecting an Account record for the Opportunity’s Potential Customer

    This then acts to limit the user’s selection of a Point of Contact to the relevant Contacts for the Opportunity:

    image

    Selecting a Contact for a particular Account via the Filtered Custom View supplied by Form Scripting”

    In determining how best to cater for these Scripted Filtered Lookups in CRM 2011 I found the following articles and supporting Javascript very useful:

    http://www.harris-tech.com/blogs/bid/54720/Creating-Filtered-Lookups-in-CRM-2011

    http://nishantrana.wordpress.com/2010/12/31/filtered-lookup-in-crm-2011/

    The Managed and Unmanaged Solution XML for this scripted ‘Point of Contact’ customisation to CRM can be downloaded via the following links:

    http://cid-0f98a78f1c3c4457.office.live.com/embedicon.aspx/.Public/PointOfContact_0_0_1_0_Managed.zip

    http://cid-0f98a78f1c3c4457.office.live.com/embedicon.aspx/.Public/PointOfContact_0_0_1_0.zip

    From these steps hopefully this gives a good idea of how Filtered Lookups can be implemented in CRM 2011 in most cases without recourse to development, and how when development is required that this can be significantly reduced and better managed than in earlier versions of CRM – with the scripting required here a good introduction to how the Xrm methods change how we can use Form Scripting in Dynamics CRM.

    The Form Scripting model of CRM 2011 is something I would like to return to in different posts, particularly the changes here from CRM 4 and the ability via Web Resources to incorporate Javascript libraries such as jQuery into Dynamics CRM Solutions to give us more flexibility in scripting as opposed to requiring more traditional server-side .NET coding.

    Advertisements
    This entry was posted in Consultancy, CRM 2011, JavaScript and tagged , , , . Bookmark the permalink.

    20 Responses to Filtered Lookups in CRM 2011

    1. Pingback: Hosted Universe » Blog Archive » Filtered Lookups in CRM 2011 | CRM Consultancy Blog_Hosted Universe

    2. Out of curiosity, I notice that when the user selects the customized lookup, the screen quickly refreshing as if processing the view change on the fly while loading the form. Is there any way to mitigate/elimate this effect? It just makes the view feel less ‘solid’ from the end user perspective.

      • Aye, I see what you mean. Did not notice that when I was originally looking at the script, is a little annoying seeing the refresh when first opening the Lookup Window.

        Looks like CRM is switching to the Default View we have set in the script immediately after the Lookup window pops up, as opposed to loading the view alongside the Lookup window – will have to see if there is a way to resolve this by having CRM pre-select the Default View as the window opens and not after.

        • snibril says:

          Did you ever find a solution to this ‘refresh’ problem? I’m having the same issue. Thanks for the code!

    3. M.Back says:

      Hi,
      has anybody tried to filter the product lookup in the quotedetail?
      We have the strange efect that the occurs, but not as default. And if you chooce the filtered view in the lookup you couldn’t select any record (OK Button is disabled).

      thanks for any hints

      regards
      Matthias

      • Luc says:

        Hi Matthias,

        I found a something that solved the same problem in my environment. Perhaps you can use the following information.

        In the layoutXML section find the line where you have to enter the ‘id’:

        var layoutXml = “” +
        “> “id=’crm_locationid’>” +

        I noticed I misspelled the attribute which cause the problem over here.

        Regards,
        Luc

    4. Luc says:

      Hello,

      I have tried to filter the product lookup in orderdetail a few months back. This worked fine, but…

      I’m trying to create another filtered lookup the same way to filter a custom entity and now I’m having the same problem as you’re having Matthias. CRM is also not alowing me to click the OK button as it’s greyed out.

      When selecting a default view, records can be selected with no problem.

      Regards
      Luc

    5. hi

      i tried this one. and when i want to click on my lookup
      it’s said that i have no sufficient permission but im administrator

      here is my code
      i tried to comment some line like the filter but it doesn’t change anything

      Code
      function FilteredLookup() {

      var viewId = “{a76b2c46-c28e-4e5e-9ddf-951b71202c9d}”;
      var entityName = “new_ville”;
      var viewDisplayName = “Villes”;

      var fetchXml = “” +
      “”+
      “”+
      “”+
      “”+
      “”+
      “”+
      ” “+
      “”+
      “”+
      “”+
      “”;

      // Part 2 –> Build Grid Layout. In other words, building a custom view for the Lookup
      //building grid layout

      var layoutXml = “” +
      “” +
      “” +
      “” +
      “” +
      “”;
      Xrm.Page.getControl(“new_ville”).addCustomView(viewId, entityName, viewDisplayName, fetchXml, layoutXml, true);
      }
      ——————————————
      LOG 1 :

      Unhandled Exception: System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=5.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]: System.Xml.XmlException: Microsoft Dynamics CRM has experienced an error. Reference number for administrators or support: #738A4E62Detail:

      -2147220970

      System.Xml.XmlException: Microsoft Dynamics CRM has experienced an error. Reference number for administrators or support: #738A4E62
      2011-11-21T11:16:32.7176339Z

      thanks to help me

    6. Luc says:

      Hello Damien,

      I don’t think I can help you out here sorry. Maybe you can check if you’re alowed to add the custom view to the attribute (the last line in your code). Perhaps the system is configured in a way that it’s not letting you do that.

      Goodluck.

    7. Thank you very much, perfect!

    8. duneel says:

      Good Job! You Rock!

    9. Zara kamruddin says:

      I just want the button “Show only my records” to be checked by Default. Is there a way to accomplish this without script??

      • You can change the default view for a Lookup Selector (with some limitations on system relationships), by customising the field from the Form Editor to open up the ‘Field Properties’ dialogue and scrolling down to the ‘Additional Properties’ area, here you can select the Default View for the Lookup and change it to ‘My Active Records’. Hope that helps.

        • Zara kamruddin says:

          Thank you for your response. I haven’t tested this in my dev environment yet, but will this “My Active Records” view…in turn…”check” the SHOW ONLY MY RECORDS box when you go to the LookupSelector?? OR…is it just a view that defaults to equals current user and the box remains unchecked?

        • It is the standard MSCRM View that will only show records where the Record Owner field equals the [Current User] (i.e. whoever is looking at the Lookup Selector)

          Alteratively you can create a new View in MSCRM with a custom criteria and set the default of the Lookup Selector to be this view.

          This ability to select which View is shown in the Lookup Selector by default is present for all custom (and some standard) Lookups in CRM 2011 as it was a common complaint against CRM 3 and 4. Similarly when you type to search the records (i.e. typing Karen to only search for contacts called Karen), this search will only look within the selected View, and not default to a standard search view in a fashion that CRM 3 and 4 did.

        • Ah sorry – think I might have misinterpreted you there. CRM 2011 also offers the ‘Show Only My Records’ checkbox, this looks at the currently selected view (whatever it may be) and introduces a further filter to the view – unfortunately to have this ticked by default would likely involve adding a piece of script as CRM automatically defaults this to being unticked.

          Might take a look to see which (if possible) script property of the Lookup control might be able to control this default setting.

    10. Joe says:

      Is there a way to set the Views (selector) to ONLY show the new custom view just created? I only want to show, and allow the users to select from, the new custom view and no others. Setting the View Selector = Off just defaults to the related field’s default lookup.

    11. Pingback: How to Ensure Contact is in Account | Everyday I'm coding

    12. I am now not certain where you’re getting your info, however great topic. I must spend a while finding out more or figuring out more. Thanks for magnificent info I used to be looking for this information for my mission.

    13. Kelsey says:

      This page truly has all of the information I needed concerning this subject and didn’t know who to ask.

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s