Filtered Lookup Approaches in CRM 2011

In this post I look at 3 different scenarios where lookup field filtering is required to illustrate the different approaches available in CRM 2011.  The out of the box options are demonstrated as well as more complex Jscript based approaches that can be adopted when the limitations of the standard functionality are hit…


Scenario 1:  You have 2 lookup fields on a form and one of those lookups should be filtered based on the other.

Example:  On the Contact form you add 2 new lookup fields, one for “Country” and one for “State”.   They map to 2 new entities: Country and State, where the Country entity has a 1:M relationship to State.  On the Contact form the user selects Country first and then when they select State the lookup should be filtered to only show the States that relate to the chosen Country.

Screen layout:

image

State lookup when Country = “Australia”:

image

State lookup when Country = “USA”:

image

Solution: Ok, this one is easy.  CRM 2011’s new Filtered Lookups functionality gives us this out of the box no code required.   The Filtered Lookup definition is set in the Properties of the State lookup on the CRM Form.  Here’s what you need to set:

image

The terminology employed here doesn’t read right to me, the fields feel like they’re placed upside down.   What we want is “Only show (State) records where the Country value of the State contains the Country value specified on this Contact record”. 

My suggestion is swap the fields in your head as you read the screen or don’t read the screen – instead just think of the first picklist as defining the input parameter and the second pick list as defining the field you want to filter on.

You can follow his approach works in those scenarios where your input parameter is a value on the form.   When your input parameter is on a related entity or needs to be hardcoded you need to go with another approach…


Scenario 2:  The same entity is represented twice on one form as 2 lookup fields.  Each lookup field should offer a different subset of records for the user to pick from.

Example:  The Account entity is used in CRM to store both Customers and Suppliers with the Relationship Type picklist on the Account form used to distinguish them.  The Contact form has 2 additional lookup fields to the Account entity, one labelled Customer, the other labelled Supplier.  The Customer lookup should only show Accounts of type “Customer” and the Supplier lookup should only show Accounts of type “Supplier”.

Screen layout:

image

Account Lookup for Customer field:

image

Account Lookup for Supplier field:

image

Solution:  There are 2 approaches to this, 1 simple, 1 complex.

The simple solution is to define 2 system views on the Account entity, 1 that only shows Customers and 1 that only shows Suppliers.  Then on the Contact form you assign the relevant view to each lookup.  Easy.

Create 2 system views:

image

Definition of the Customer lookup:

image

Definition of the Supplier lookup:

image

So this approach works fine in most simple scenarios.  The downside is that the 2 system views created to support this are visible throughout CRM.  In this scenario that’s probably not a big deal, the 2 views may be useful to end users, but once the volume of views increases things start to get messy. 

The workaround to this is to dynamically define new lookup views in jscript on form load and instruct the lookup fields to use these new views instead.  Here’s some sample jscript code that demonstrates how to do this.  Place this in a web resource and call the 2 functions provided from the Contact form’s OnLoad event:

function ChangeLookupView_Customer() { 
    //Note: in the form designer make sure the lookup field is set to "Show all views" in its "View Selector" property  

    //Set parameters values needed for the creation of a new lookup view... 
    var viewId = Xrm.Page.data.entity.getId();      // Your new lookup views needs a unique id.  It must be a GUID.  Here I use the GUID of the current record. 
    var entityName = "account";                     // The entity your new lookup view relates to 
    var viewDisplayName = "Account - Customers";    // A name for new lookup view 
    var viewIsDefault = true;                       // Whether your new lookup view should be the default view displayed in the lookup or not 

    //Define the Fetch XML query your new lookup view will use.  You can create this via Advanced Find.  You'll need to massage the syntax a little though 
    var fetchXml =  
              "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>" + 
              "<entity name='account'>" + 
              "<attribute name='name' />" + 
              "<attribute name='accountid' />" + 
              "<attribute name='customertypecode' />" + 
              "<filter type='and'>" + 
              "<condition attribute='customertypecode' operator='eq' value='3' />" + 
              "</filter>" + 
              "<order attribute='name' decending='false' />" + 
              "</entity>" + 
              "</fetch>"; 

    //Define the appearance of your new lookup view 
    var layoutXml =  
                    "<grid name='resultset' object='1' jump='name' select='1' icon='1' preview='1'>" +   
                    "<row name='result' id='accountid'>" +  // id = the GUID field from your view that the lookup should return to the CRM form 
                    "<cell name='name' width='200' />" +   
                    "<cell name='customertypecode' width='200' />" + 
                    "</row>" + 
                    "</grid>"; 
 
    //Add your new view to the Lookup's set of availabe views and make it the default view 
    Xrm.Page.getControl("new_customer").addCustomView(viewId, entityName, viewDisplayName, fetchXml, layoutXml, viewIsDefault); 
} 


function ChangeLookupView_Supplier() { 

    //Set parameters values needed for the creation of a new lookup view... 
    var viewId = Xrm.Page.data.entity.getId();  // Using the same GUID, the viewId only has to be unique within each lookup's set of views 
    var entityName = "account"; 
    var viewDisplayName = "Account - Suppliers"; 
    var viewIsDefault = true; 

    //Define the Fetch XML query your new lookup view will use   
    var fetchXml = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>" + 
          "<entity name='account'>" + 
          "<attribute name='name' />" + 
          "<attribute name='accountid' />" + 
          "<attribute name='customertypecode' />" + 
          "<filter type='and'>" + 
          "<condition attribute='customertypecode' operator='eq' value='10' />" +   // A different value provided here to produce a different result set 
          "</filter>" + 
          "<order attribute='name' decending='false' />" + 
          "</entity>" + 
          "</fetch>"; 

    //Define the appearance of your new lookup view 
    //No changes required here compared to the above, the lookups should have the same appearance 
    var layoutXml = "<grid name='resultset' object='1' jump='name' select='1' icon='1' preview='1'>" +   
             "<row name='result' id='accountid'>" + 
              "<cell name='name' width='200' />" + 
              "<cell name='customertypecode' width='200' />" + 
             "</row>" + 
            "</grid>"; 

    //Add your new view to the Lookup's set of availabe views and make it the default view 
    //The supplier field is specified here, we want to add this view to that lookup field 
    Xrm.Page.getControl("new_supplier").addCustomView(viewId, entityName, viewDisplayName, fetchXml, layoutXml, viewIsDefault); 
}

 

Rather than talk you through the code in this post I’ve put a lot of comments into the code so have a read through that.   2 tricks that I stumbled on are:

1) The view ID that you assign to your new view must be a GUID value.

2) In the form designer make sure your lookup field has "Show all views" set in its "View Selector" property.


Scenario 3:  A lookup field needs to be filtered based on a lookup field that resides on a related record.

Example:  The Account form has a Country field, the Contact form a State field.  When selecting the State on the Contact only those States related to the Country specified on the Contact’s parent Account should be offered to the user.

Screen layout – Account screen:

image

Screen layout – Contact screen:

image

State lookup on Contact form when parent Account’s Country = “USA”:

image

Solution:

This is the trickiest scenario to address because the filtering needs to be based on a piece of data that is not available to us on the form.  We can get to that data though and my preferred approach at the moment for this is to make an ODATA query to CRM’s REST end point.   So, our approach is as follows:

– Read the Parent Account GUID value from the Contact form

– Execute an ODATA query along the lines of: “Select the Country field from the Account entity where the Account’s GUID = this Contact’s Parent Account GUID”.

– Once we have that Country value we can then dynamically create a new view and assign it to the State lookup field on the Contact form just like we did in the last scenario.

Here’s the jscript that achieves this:

function GetCountryFromAccount() { 
    //Get the GUIDvalue of this Contact's Parent Account 
    var ParentAccount = Xrm.Page.data.entity.attributes.get("parentcustomerid"); 
    if (ParentAccount.getValue() != null) { 
        var ParentAccountGUID = ParentAccount.getValue()[0].id; 
    } 

    // Read the CRM Context to determine the CRM URL 
    var serverUrl = Xrm.Page.context.getServerUrl() 

    // Specify the ODATA End Point and Entity Collection 
    var ODATA_ENDPOINT = "XRMServices/2011/OrganizationData.svc"; 
    var ODATA_EntityCollection = "/AccountSet"; 

    // Specify the ODATA Query 
    var ODATA_Query = "(guid\'" + ParentAccountGUID + "\')?$select=new_Country"; 

    // Combine into the final URL 
    var ODATA_Final_url = serverUrl + ODATA_ENDPOINT + ODATA_EntityCollection + ODATA_Query; 

    //Calls the REST endpoint to retrieve the Country value of the Parent Account 
    $.ajax({ 
        type: "GET", 
        contentType: "application/json; charset=utf-8", 
        datatype: "json", 
        url: ODATA_Final_url, 
        beforeSend: function (XMLHttpRequest) { 
            XMLHttpRequest.setRequestHeader("Accept", "application/json"); 
        }, 
        success: function (data, textStatus, XmlHttpRequest) { 
            //This function will trigger asynchronously if the Retrieve was successful 
            var CountryGUID = data.d.new_Country.Id; 
            if (CountryGUID == null) { 
                // country on acct is null, we won't bother filtering the view 
                return; 
            } 
            else { 
                // call function to add custom view 
                ChangeLookupView_State(CountryGUID); 
            } 
        }, 
        error: function (XmlHttpRequest, textStatus, errorThrown) { 
            //This function will trigger asynchronously if the Retrieve returned an error 
            //Error encountered, we won't bother filtering the view 
            return; 
        } 
    }); 
} 

function ChangeLookupView_State(CountryGUID) { 
    //Set parameters values needed for the creation of a new lookup view... 
    var viewId = Xrm.Page.data.entity.getId();              // Your new lookup views needs a unique id.  It must be a GUID.  Here I use the GUID of the current record. 
    var entityName = "new_state";                           // The entity your new lookup view relates to 
    var viewDisplayName = "States for Account Country";     // A name for new lookup view 
    var viewIsDefault = true;                               // Whether your new lookup view should be the default view displayed in the lookup or not 

    //Define the Fetch XML query your new lookup view will use.  You can create this via Advanced Find.  You'll need to massage the syntax a little though 
    var fetchXml = 
              "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>" + 
              "<entity name='new_state'>" + 
              "<attribute name='new_stateid' />" + 
              "<attribute name='new_name' />" + 
              "<attribute name='new_countryid' />" + 
              "<order attribute='new_name' descending='false' />" + 
              "<filter type='and'>" + 
              "<condition attribute='new_countryid' operator='eq' value='" + CountryGUID + "' />" + 
              "</filter>" + 
              "</entity>" + 
              "</fetch>"; 

    //Define the appearance of your new lookup view 
    var layoutXml = 
                    "<grid name='resultset' object='1' jump='name' select='1' icon='1' preview='1'>" + 
                    "<row name='result' id='new_stateid'>" +  // id = the GUID field from your view that the lookup should return to the CRM form 
                    "<cell name='new_name' width='200' />" + 
                    "</row>" + 
                    "</grid>"; 

    //Add your new view to the Lookup's set of availabe views and make it the default view 
    Xrm.Page.getControl("new_statelookupid").addCustomView(viewId, entityName, viewDisplayName, fetchXml, layoutXml, viewIsDefault); 
}

Note: Because I’m following an oDATA approach this jscript needs the jquery and json2 libraries loaded into your CRM instance as web resources and needs those web resources referenced on the CRM form.  I talk more about these prerequisite steps in an earlier post.

 

Footnote:

The more I play with filtered lookups the more evident it becomes that all you are really able to do is improve the user experience, you are not able to effectively implement any sort of validation control, there are too many loopholes that will allow users to pick values that disobey the default filtering behaviour.   So, in addition to configuring your filtered lookups you really need to build out validation rules, either via jscript or Plug-in.

Advertisements

16 thoughts on “Filtered Lookup Approaches in CRM 2011

  1. Kelvin

    Hi Gareth,

    Your code sample (Senario 3) worked brilliantly! Thanks!

    However, maybe you will want to change the first statement under ChangeLookupView_State function from var viewId = Xrm.Page.data.entity.getId(); to var viewId = Xrm.Page.context.getUserId();

    This way your code will work on record creation too.

    Cheers

    K

    Reply
  2. Sudheera

    Hi Gareth,

    Thousand thanks for your samples here. I was stagnating for more than 2.5 days to find a solution for Scenario 03. Your post help lot to cure a severe headache.
    Thanks again.

    @ Kelvin : Your comment also help me. Thanks lot.

    Cheers
    Sudheera

    Reply
  3. awad

    hi Gareth,

    Your example get the correct data but its not sorted though you are using order tagin your code .

    Would you please tell me how to apply sorting on CustomLookupViews..?

    I have been searching for the solution on web but i didnt find. would you please help me..?

    Reply
    1. Gareth Tucker Post author

      I don’t know sorry, perhaps someone else out there reading this knows the answer. If you figure it out please let me know and I can add the solution to this post.

      Reply
      1. Matt (Microsoft)

        Hi, you are correct, there is currently not a way to sort the lookups however it is something being added for one of the upcoming Update Rollups.
        Thanks,
        Matt

  4. Mostafa El Moatassem bellah

    Hi,
    First of all thanks alot for this very useful post

    Second:

    I have a requirement to set the default view to be displayed in a opportunity lookup field based on on the selected value of an optionset
    the easiest way to do this is to create two custom views and to insert the code to display them in the onChange event of the the optionset value.
    But what if I already have the two views as system views, is thee anyway to use them instead of creating two custom views ??

    Thanks and best regards..

    Reply
    1. Gareth Tucker Post author

      Have a look in my jscript reference post at the example for changing the default entity offered up by customer lookup fields. I think this will point you in the right direction.

      Reply
  5. rama

    hi gareth,

    How can we call a webservice to load records in a lookup view? i have scenario where the entity does not store records, but on its lookup click, it should call a web resource and load data??

    Reply
  6. didge

    Great post, well structured and explained.

    Have you come across the issue when using the filtering method described in Scenario 3…

    Whereby, only the first filter is set, any subsequent calls to the filtering method appear to do nothing. Both calls having different viewDisplayName values, but the first viewDisplayName remains in the view list after the second call. (during the same form session).

    Cheers 🙂

    Reply
  7. didge

    fixed!!
    re-enable view picker before adding new default custom view

    document.getElementById(fieldName).disableViewPicker = 0;

    Xrm.Page.getControl(fieldName).addCustomView(viewId, entityName, viewDisplayName, fetchXml, layoutXml, viewIsDefault);

    document.getElementById(fieldName).disableViewPicker = 1;

    Reply
  8. Pingback: CRM 2011 Filtered Lookup | SharePoint and CRM Revealed

  9. Arun

    i have two lookup fields referring to the same entity(business unit), my customization is like, the second lookup field view should displays values – according to the first lookup field, i.e., according to the parentbusinessunit selected in first lookup fied, the second lookup field should displays child bu of the parent bu selected.

    I achieved it in onchange but, when i save the form, the record in first lookup field is populated again in the other lookup field, view is perfect, but the values are not getting saved.

    can u suggest some ideas for this..

    Reply
  10. gary

    hi,
    Great post. i used the scenario 2 in my situation ..works great but have an issue on save of the form, my lookup field becomes empty. any idea how to fix.

    Thanks.
    Gary

    Reply

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