Mittwoch, 26. Oktober 2011

Filtered Lookup as a Drop-down Picklist in MSCRM

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
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
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) {
016 try {
017 var includeAdditionalAttribute = false;
019 filteredDropdown.length = 0;
020 if (allowNull == true) {
021 filteredDropdown.AddOption("", null);
022 }
024 // determine whether to inclue an additional attribute in the drop-down list (such as Job Title)
025 if (recordAdditionalAttribute != "") {
026 includeAdditionalAttribute = true;
027 }
029 // Build the Fetch XML Message to run against the MSCRM Webservice
030 var fetchXml =
031 "<fetch mapping=\"logical\">" +
032 "<entity name=\"" + entityType + "\">" +
033 "<attribute name=\"" + recordIdAttribute + "\" />" +
034 "<attribute name=\"" + recordNameAttribute + "\" />";
036 if (includeAdditionalAttribute == true) {
037 fetchXml += "<attribute name=\"" + recordAdditionalAttribute + "\" />";
038 }
040 fetchXml += filterCondition +
041 "</entity>" +
042 "</fetch>";
044 // Invoke function (defined above) to send the XML to the MSCRM Webservice
045 var resultXml = FetchXmlResponse(fetchXml);
047 var oXmlDoc = new ActiveXObject("Microsoft.XMLDOM");
048 oXmlDoc.async = false;
049 oXmlDoc.loadXML(resultXml.text);
051 // Parse the returned XML Response returned from the MSCRM Webservice
052 var result = oXmlDoc.getElementsByTagName('result');
054 if (result != null) {
055 var n;
056 var lookupItem = new Array;
057 var optionText;
058 var optionSelected = false;
060 lookupItem = relationshipAttribute.DataValue;
062 // cycle through results returned from the MSCRM Web Service
063 for (n = 0; n <= (result.length - 1); n++) {
064 var record_id = result[n].getElementsByTagName(recordIdAttribute);
065 var record_name = result[n].getElementsByTagName(recordNameAttribute);
067 if (record_name[0] != null) {
068 optionText = record_name[0].text;
069 }
071 if (includeAdditionalAttribute == true) {
072 var record_additional = result[n].getElementsByTagName(recordAdditionalAttribute); ;
074 if (record_additional[0] != null) {
075 optionText += ", " + record_additional[0].text;
076 }
077 }
079 filteredDropdown.AddOption(optionText, n);
081 dropdownOptionText[n] = record_name[0].text;
082 dropdownOptionValues[n] = record_id[0].text;
084 if (lookupItem != null) {
085 if (lookupItem[0].id == record_id[0].text) {
086 filteredDropdown.DataValue = n;
087 optionSelected = true;
088 }
089 }
090 }
092 // has the dropdown selected option being populated by a pre-defined value in the relationship?
093 if (optionSelected == false) {
094 if (result.length > 0) {
095 if (defaultSelect == true) {
096 // default select to the first option populated
097 filteredDropdown.DataValue = 1;
098 }
099 // otherwise a blank value will be selected
100 }
101 }
103 filteredDropdown.onchange = function() {
104 DropdownSelection(filteredDropdown, entityType, relationshipAttribute);
105 };
106 }
107 }
108 catch (ex) {
109 alert("PopulateDropdown - " + ex.description + ".");
110 }
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:
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) {
08 try {
09 var lookupData = new Array();
10 var lookupItem = new Object();
12 // check that the Picklist is not blank
13 if (filteredDropdown.DataValue != null) {
14 // build Lookup Value to populate for the Lookup field in the relationship
15 = dropdownOptionValues[filteredDropdown.DataValue];
16 lookupItem.typename = entityType;
17 = dropdownOptionText[filteredDropdown.DataValue];
18 lookupData[0] = lookupItem;
20 // populate the Lookup field in the relationship as the Lookup Value
21 relationshipAttribute.DataValue = lookupData;
22 relationshipAttribute.ForceSubmit = true;
23 }
24 else {
25 // populate the Lookup field in the relationship as Blank
26 relationshipAttribute.DataValue = null;
27 relationshipAttribute.ForceSubmit = true;
28 }
29 }
30 catch (ex) {
31 alert("DropdownSelection - " + ex.description + ".");
32 }
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 {
02 // Main
03 if (crmForm.FormType == 2) {
04 var filterCondition = "<filter type='and'>" +
05 "<condition attribute='parentcustomerid' operator='eq' value='" + crmForm.ObjectId + "'/>" +
06 "<condition attribute='statecode' operator='eq' value='" + "0" + "'/>" +
07 "</filter>";
09 PopulateDropdown(crmForm.all.new_contactdropdown, filterCondition, "contact", "contactid", "fullname", "jobtitle", crmForm.all.primarycontactid, true, false);
10 }
12catch (ex) {
13 alert("Main - " + ex.description + ".");
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 )
3 crmForm.all.new_contactdropdown.DataValue = null;
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
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
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() {
02 try {
03 var customerAccountId = null;
04 var supplierAccountId = null;
06 var custLookupItem = new Array;
07 custLookupItem = crmForm.all.customerid.DataValue;
09 if (custLookupItem != null) {
10 customerAccountId = custLookupItem[0].id;
11 }
13 var suppLookupItem = new Array;
14 suppLookupItem = crmForm.all.new_supplieraccountid.DataValue;
16 if (suppLookupItem != null) {
17 supplierAccountId = suppLookupItem[0].id;
18 }
20 if ((customerAccountId != null) && (supplierAccountId != null)) {
21 var filterCondition = "<condition attribute='new_customeraccountid' operator='eq' value='" + customerAccountId + "' />" +
22 "<condition attribute='new_supplieraccountid' operator='eq' value='" + supplierAccountId + "' />" +
23 "<condition attribute='statecode' operator='eq' value='" + "0" + "' />";
24 PopulateDropdown(crmForm.all.new_accountnumber, filterCondition, "new_buyingrelationship", "new_buyingrelationshipid", "new_name", "new_accountnumber", crmForm.all.new_buyingrelationshipid, true, true);
25 }
26 }
27 catch (ex) {
28 alert("PopulateBuyingRelationshipsList - " + ex.description + ".");
29 }
32try {
33 // Main
34 PopulateBuyingRelationshipsList();
36catch (ex) {
37 alert("Main - " + ex.description + ".");
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
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.
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.

Twitter Delicious Facebook Digg Stumbleupon Favorites More

Design by Free WordPress Themes | Bloggerized by Lasantha - Premium Blogger Themes | Free Samples By Mail