Tag Archives: filtered lookups

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

Filtered Lookups in 2011 – The Basics

Here’s a quick example of configuring a filtered lookup in 2011. 

In this scenario we want to be able to specify on an Opportunity who the Key Customer Contact is and when selecting this Contact we want the lookup to only offer up the Contacts attached to the Opportunity Customer.

1.  Let’s start by adding a Key Customer Contact field to the Opportunity entity / form.   Note: I love how much more fluent this process is in 2011…

a. Open the Opportunity form, click on the Customize ribbon tab and then click Form.  The Form Designer will launch.

image

b. Click the New Field button.  The New Field dialog will display.

image

c. Enter “Key Customer Contact” as the Display Name for your new field, select Lookup as the Type, and then select Contact as the Target Record Type.  Save and Close.  The field will be created and you will be returned to the Form Designer.

image

d. Your new Key Customer Contact field will appear in the Field Explorer list, drag it onto the Opportunity form

image

2.  At this point you might like to test things, so do a Save & Close of the Form Designer – you will be returned to the Opportunity form – click the All Customisations button on the Customize menu, and then refresh your browser window.

You should see your new Key Customer Contact field on the form… image

… and if you open its lookup  you should see the lookup displays all Contacts:image

3.  Ok, let’s now add the filtering.   Open up the Form Designer again (click on the Customize ribbon tab and then click Form). 

4.  Double-click on the Key Customer Contact field – the Field Properties dialog will display. 

5.  The relevant settings are in the last 2 sections – here’s how they will look initially

image

Note: there is no related record filtering going on.  The lookup dialog is set to display the “Contacts Lookup View” with other all other Contact views able to be selected and the Search box enabled.

6.  To enable the filtering we check the Only show records where: option in the Related Records Filtering section

image

7.  Next, we need to specify the source value from the Opportunity record to pass into the lookup and the destination field on the Contact entity on which to filter.  In our scenario the source value is the Potential Customer field on the Opportunity form and the field on the Contact on which we wish to filter is the Parent Customer field.

image

8. We need to decide whether this restricted list needs to be enforced or whether we are only doing this to be helpful and users should be able to optionally select a Contact from another Organisation.  In this scenario we want to enforce this so we uncheck the Allow users to turn off filter

image

9.  That’s it, you can save, publish and refresh to see the filtering in action.   Optionally, you can remove some of the clutter from the lookup dialog by disabling the display of the search box and turning off the View Selector

image

 

Here’s the end result – the Key Customer Contact lookup dialog now only offering the Contacts that are related to the Opportunity Customer:

image

 

Finally, some words of caution on this feature – the filtering  is only applied at run time as the user is populating the lookup field, it is not enforced after that point.  i.e. If having selected a Customer and a Key Customer Contact you then change the Customer field the Key Customer Contact field is not re-validated, CRM will allow the field to retain a value that would not be offered up by the filtered lookup.   

Think of this feature as a helper for your users, not as means of enforcing data integrity.  For that level of control you will need to consider some jscript or plugin code.