Filtered Lookup as a Drop-down Picklist in Microsoft Dynammics CRM

The ability to add a Filtered Lookup is one of those areas of core functionality that MSCRM currently lacks – this thankfully will be resolved in the forthcoming v5 of MSCRM, however at the moment we are forced to use extensions or customisations to replicate this functionality.
Given the extendibility of the platform, there are many methods for implementing a Filtered Lookup in MSCRM:
- Use a Custom Developed ASP.NET screen to select and then populate the relationship.
- Purchase the popular Stunnware Filtered Lookup tool here.
- Hack the existing Lookup field type and screen to contain a custom filter, this is completely unsupported and so not advised but you can find details on this here.
And I am sure there are many other methods beyond, this post aims to outline a simple pure-Javascript method of implementing a Filtered Lookup as a Picklist. This method has natural advantages and disadvantages but can be a simple solution for when time or budget may be an issue.

Drop-down selection of a Primary Contact for an Account

STEP 1 – Add a Dummy Picklist field to the Entity involved
This method aims to use a Picklist as the user interface for a drop-down selection of the Filtered Lookup, as such we need to create this Picklist as a new field. This is wasteful as the Picklist will simply be used for the presentation and not actually store anything, however this simply allows us to maintain a set of picklist values:

Adding a dummy Picklist attribute to the Account screen in MSCRM in order to allow for a Filtered Drop-down selection of a Primary Contact

As a side note, we could use onLoad scripting to add a SELECT HTML Tag into the Entity screen to create a temporary non-MSCRM field Picklist, however this would involve injecting HTML into the Form’s Document Object Model and so would push us into unsupported territory.
STEP 2 – Add a Javascript Function to Populate the Dummy Picklist
We can implement this Filtered Drop-down via a series of Javascript functions within the onLoad script of a particular entity – starting with a function to populate the initial list of drop-down options:

 
001/// <summary>

002/// Populates a particular Picklist field with values retrieved from the MSCRM Web Service

003/// </summary>

004/// <param name="filteredDropdown">The CRM Picklist control to use as the Filtered Drop-down</param>

005/// <param name="filterCondition">XML describing the CRM Filter for retrieving the records for the filtered drop-down</param>

006/// <param name="entityType">Describes the Entity Type involved for retrieving the records for the filtered drop-down</param>



008/// <param name="recordIdAttribute">Specifies the Database name of the Primary Key field for the Entity Type involved - should always be [entityType] + 'id'</param>

009/// <param name="recordNameAttribute">Specifies the Database name of a String Name field to retrieve for the Drop-down</param>

010/// <param name="recordAdditionalAttribute">Optional, specifies the Database name of an additional field to retrieve for the Drop-down</param>



012/// <param name="relationshipAttribute">Specifies the CRM Lookup control to populate the relationship when a value is chosen from the Filtered Picklist</param>

013/// <param name="allowNull">Specifies whether the filtered Picklist should include a Blank option</param>

014/// <param name="defaultSelect">Specifies whether the filtered Picklist automatically selects the first option upon population</param>

015PopulateDropdown = function(filteredDropdown, filterCondition, entityType, recordIdAttribute, recordNameAttribute, recordAdditionalAttribute, relationshipAttribute, allowNull, defaultSelect) {

  try {

      var includeAdditionalAttribute = false;



      filteredDropdown.length = 0;

      if (allowNull == true) {

          filteredDropdown.AddOption("", null);

      }



      // determine whether to inclue an additional attribute in the drop-down list (such as Job Title)

      if (recordAdditionalAttribute != "") {

          includeAdditionalAttribute = true;

      }



      // Build the Fetch XML Message to run against the MSCRM Webservice

      var fetchXml =

          "<fetch mapping=\"logical\">" +

              "<entity name=\"" + entityType + "\">" +

                  "<attribute name=\"" + recordIdAttribute + "\" />" +

                  "<attribute name=\"" + recordNameAttribute + "\" />";



      if (includeAdditionalAttribute == true) {

          fetchXml += "<attribute name=\"" + recordAdditionalAttribute + "\" />";

      }



      fetchXml += filterCondition +

              "</entity>" +

          "</fetch>";



      // Invoke function (defined above) to send the XML to the MSCRM Webservice

      var resultXml = FetchXmlResponse(fetchXml);



      var oXmlDoc = new ActiveXObject("Microsoft.XMLDOM");

      oXmlDoc.async = false;

      oXmlDoc.loadXML(resultXml.text);



      // Parse the returned XML Response returned from the MSCRM Webservice

      var result = oXmlDoc.getElementsByTagName('result');



      if (result != null) {

          var n;

          var lookupItem = new Array;

          var optionText;

          var optionSelected = false;



          lookupItem = relationshipAttribute.DataValue;



          // cycle through results returned from the MSCRM Web Service

          for (n = 0; n <= (result.length - 1); n++) {

              var record_id = result[n].getElementsByTagName(recordIdAttribute);

              var record_name = result[n].getElementsByTagName(recordNameAttribute);



              if (record_name[0] != null) {

                  optionText = record_name[0].text;

              }



              if (includeAdditionalAttribute == true) {

                  var record_additional = result[n].getElementsByTagName(recordAdditionalAttribute); ;



                  if (record_additional[0] != null) {

                      optionText += ", " + record_additional[0].text;

                  }

              }



              filteredDropdown.AddOption(optionText, n);



              dropdownOptionText[n] = record_name[0].text;

              dropdownOptionValues[n] = record_id[0].text;



              if (lookupItem != null) {

                  if (lookupItem[0].id == record_id[0].text) {

                      filteredDropdown.DataValue = n;

                      optionSelected = true;

                  }

              }

          }



          // has the dropdown selected option being populated by a pre-defined value in the relationship?

          if (optionSelected == false) {

              if (result.length > 0) {

                  if (defaultSelect == true) {

                      // default select to the first option populated

                      filteredDropdown.DataValue = 1;

                  }

                  // otherwise a blank value will be selected

              }

          }



          filteredDropdown.onchange = function() {

              DropdownSelection(filteredDropdown, entityType, relationshipAttribute);

          };

      }

  }

  catch (ex) {

      alert("PopulateDropdown - " + ex.description + ".");

  }

111}

This relies on other Javascript functions to send a XML Message to the MSCRM Web Service in order to receive a XML Response back from the MSCRM Web Service – this is common technique when working with MSCRM (althrough not one to be over-used as Javascript can quickly bloat for the various onLoad events) with a number of possible implementations.
The following script, which is re-used several times across this blog, is one possible implementation of this:

01function htmlEncode(source, display, tabs) {

  function special(source) {

      var result = '';

      for (var i = 0; i < source.length; i++) {

          var c = source.charAt(i);

          if (c < ' ' || c > '~') {

              c = '&#' + c.charCodeAt() + ';';

          }

          result += c;

      }

      return result;

  }



  function format(source) {

      // Use only integer part of tabs, and default to 4

      tabs = (tabs >= 0) ? Math.floor(tabs) : 4;

      // split along line breaks

      var lines = source.split(/\r\n|\r|\n/);



      // expand tabs

      for (var i = 0; i < lines.length; i++) {

          var line = lines[i];

          var newLine = '';

          for (var p = 0; p < line.length; p++) {

              var c = line.charAt(p);

              if (c === '\t') {

                  var spaces = tabs - (newLine.length % tabs);

                  for (var s = 0; s < spaces; s++) {

                      newLine += ' ';

                  }

              }

              else {

                  newLine += c;

              }

          }

          // Leading or ending spaces will be removed from a HTML Request, unless flagged as a nbsp type character

          newLine = newLine.replace(/(^ )|( $)/g, '&nbsp;');

          lines[i] = newLine;

      }

      // re-join lines

      var result = lines.join('<br />');

      // break up contiguous blocks of spaces with non-breaking spaces

      result = result.replace(/  /g, ' &nbsp;');



      return result;

  }

  var result = source;



  // ampersands (&)

  result = result.replace(/\&/g, '&amp;');

  // less-thans (<)

  result = result.replace(/\</g, '&lt;');

  // greater-thans (>)

  result = result.replace(/\>/g, '&gt;');



  if (display) {

      // format for display

      result = format(result);

  }

  else {

      // Replace quotes if it isn't for display,

      // since it's probably going in an html attribute.

      result = result.replace(new RegExp('"', 'g'), '&quot;');

  }



  result = special(result);

  return result;

68}



70// Fires an XML Message to the MSCRM Web Service

71FetchXmlResponse = function(fetchXml) {

  try {

      var xml =

          "<?xml version=\"1.0\" encoding=\"utf-8\" ?>" +

          "<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">" +

              GenerateAuthenticationHeader() +

              "<soap:Body>" +

                  "<Fetch xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">" +

                      "<fetchXml>" + htmlEncode(fetchXml) + "</fetchXml>" +

                  "</Fetch>" +

              "</soap:Body>" +

          "</soap:Envelope>";



      var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");

      xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);

      xmlHttpRequest.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/crm/2007/WebServices/Fetch");

      xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");

      xmlHttpRequest.setRequestHeader("Content-Length", xml.length);



      xmlHttpRequest.send(xml);



      return xmlHttpRequest.responseXML;

  }

  catch (ex) {

      alert("FetchXMLResponse - " + ex.description + "");

  }

97}

Microsoft post another possible implementation of this as Scripting Sample Code here. There is also a good detailed description of how and why this AJAX concept works (as opposed to lines of script) here on Customer Effective’s shared blog.
STEP 3 – Add a second Javascript Function for populating the real Lookup relationship upon selection of the Dummy Picklist
However looking back at the first piece of scripting, the highlighted line in the script invokes another Javascript function to ensure that any selection made in the Filtered Drop-down populates the actual Lookup relationship.

 
01/// <summary>

02/// Function that runs when a drop-down value has been selected in our custom Filtered Drop-down

03/// </summary>

04/// <param name="filteredDropdown">The CRM Picklist control used as the Filtered Drop-down</param>

05/// <param name="entityType">Describes the Entity Type involved for the relationship</param>

06/// <param name="relationshipAttribute">Specifies the CRM Lookup control to populate with the selected value for the relationship</param>

07DropdownSelection = function(filteredDropdown, entityType, relationshipAttribute) {

  try {

      var lookupData = new Array();

      var lookupItem = new Object();



      // check that the Picklist is not blank

      if (filteredDropdown.DataValue != null) {

          // build Lookup Value to populate for the Lookup field in the relationship

          lookupItem.id = dropdownOptionValues[filteredDropdown.DataValue];

          lookupItem.typename = entityType;

          lookupItem.name = dropdownOptionText[filteredDropdown.DataValue];

          lookupData[0] = lookupItem;



          // populate the Lookup field in the relationship as the Lookup Value

          relationshipAttribute.DataValue = lookupData;

          relationshipAttribute.ForceSubmit = true;

      }

      else {

          // populate the Lookup field in the relationship as Blank

          relationshipAttribute.DataValue = null;

          relationshipAttribute.ForceSubmit = true;

      }

  }

  catch (ex) {

      alert("DropdownSelection - " + ex.description + ".");

  }

33}

For these two Javascript functions to function together – we need to declare a ‘global’ pair of arrays in the script to map a value selected in the Filtered Drop-down to the actual GUID value that needs to be populated into the associated Lookup field.

 
1// 'global' arrays to store assocation between values in Filtered Dropdown and Relationship values

2var dropdownOptionText = new Array();

3var dropdownOptionValues = new Array();

STEP 4 – Invoke the functions with the relevant Relationship Values
Finally with these pieces of script in place on our onLoad event – we can invoke a Main (i.e. non-function) piece of scripting to invoke these scripting functions for the particular Picklist field, Relationship and Lookup field that we wish to filter:

 
01try {

  // Main

  if (crmForm.FormType == 2) {

      var filterCondition = "<filter type='and'>" +

                              "<condition attribute='parentcustomerid' operator='eq' value='" + crmForm.ObjectId + "'/>" +

                              "<condition attribute='statecode' operator='eq' value='" + "0" + "'/>" +

                            "</filter>";



      PopulateDropdown(crmForm.all.new_contactdropdown, filterCondition, "contact", "contactid", "fullname", "jobtitle", crmForm.all.primarycontactid, true, false);

  }

11}

12catch (ex) {

  alert("Main - " + ex.description + ".");

14}

We can implement this via the following Javascript to produce a filtered drop-down listing Contacts that are associated to a particular Account, which then allows the CRM User to select a particular Primary Contact from the filtered drop-down.
The final point here is to wipe the selected value of the Dummy Picklist on save of the form with the following onSave script:

 
1if ( crmForm.all.new_contactdropdown.DataValue != null )

2{

3  crmForm.all.new_contactdropdown.DataValue = null;

4}

This is an unfortunate step as is hacking/changing the user’s entered DataValue, however is mostly unavoidable given how we are using the Picklist. (we could avoid this by creating a number of dummy selections in the Picklist’s possible values – however this is tricky as in most cases we cannot predict how many selections the picklist is going to have from the Filter Condition.)
Practical Example
The above scripting produces a drop-down list of Contacts from the Account screen, however I would rarely suggest using several large pieces of script to solve such a simple problem and would more often advise a client that the non-filtered selection of a Primary Contact is simply a current limitation of MSCRM.
However in other instances this concept of using a Filtered Lookup can be much more key to a project – this final section of the post aims to show one particular instance as way of an example of this.
If we take a business model which handles Orders between Customers and Suppliers, such that each Sales Order has a relationship to a Customer Account and a Supplier Account, and also needs to track an Account Number or Relationship Number that denotes the relationship between the particular Customer and Supplier:

Entity Relationship Digram for showing an example of using this Filtered Lookup concept

In this instance, we may want the CRM User to specify a Customer and Supplier for an Order and then be presented with the list of Selling Relationships (if present) that link this Customer to the Supplier as way of authorising the purchase between the two parties.

Selecting the Buying Relationship via non-filtered Lookup between the Customer Account and Supplier Account

By default MSCRM Behaviour, this will be a Buying Relationship Lookup showing a full list of the Buying Relationship records regardless of the Customer or Supplier selected. This would be a fairly negative experience for the User to have to search for the particular Buying Relationship records that applied for the Customer and Supplier they had already specified.
To improve this, we can use the script outlined earlier amended slightly to refresh a Buying Relationship Drop-down based on the Customer and Supplier selected:

 
01PopulateBuyingRelationshipsList = function() {

  try {

      var customerAccountId = null;

      var supplierAccountId = null;



      var custLookupItem = new Array;

      custLookupItem = crmForm.all.customerid.DataValue;



      if (custLookupItem != null) {

          customerAccountId = custLookupItem[0].id;

      }



      var suppLookupItem = new Array;

      suppLookupItem = crmForm.all.new_supplieraccountid.DataValue;



      if (suppLookupItem != null) {

          supplierAccountId = suppLookupItem[0].id;

      }



      if ((customerAccountId != null) && (supplierAccountId != null)) {

          var filterCondition =   "<condition attribute='new_customeraccountid' operator='eq' value='" + customerAccountId + "' />" +

                                  "<condition attribute='new_supplieraccountid' operator='eq' value='" + supplierAccountId + "' />" +

                                  "<condition attribute='statecode' operator='eq' value='" + "0" + "' />";

          PopulateDropdown(crmForm.all.new_accountnumber, filterCondition, "new_buyingrelationship", "new_buyingrelationshipid", "new_name", "new_accountnumber", crmForm.all.new_buyingrelationshipid, true, true);

      }

  }

  catch (ex) {

      alert("PopulateBuyingRelationshipsList - " + ex.description + ".");

  }

30}



32try {

  // Main

  PopulateBuyingRelationshipsList();

35}

36catch (ex) {

  alert("Main - " + ex.description + ".");

38}

With this in place, we can hide the actual Buying Relationship Lookup field in the Administration tab and show the Dummy Picklist field on the General tab to present a simple selection of Buying Relationships once a Customer or Supplier has been selected.
NOTE: The Customer and Supplier onChange events would naturally need to invoke the PopulateBuyingRelationshipsList() function to facilitate this happening on selection.

Dummy Picklist field showing the list of possible Buying Relationships between the selected Customer and Supplier

This gives a much simpler selection – with the added benefit that via the [defaultSelect] parameter we saw in the earlier script the first Buying Relationship between the Customer and Supplier will immediately be selected as the default, this may also aid the User Experience.
Summary
Filtered Lookup functionality will be standard customisation in the forthcoming MSCRM v5, however in the meantime, the ability here to add a simple Filtered Dropdown through Form Scripting can be a useful technique – particularly for smaller lists of information or when the filter needs to change in real-time.

Read more »