Overview
One very helpful option added to Microsoft Dynamics CRM 2011 is the ability to apply custom filtering to a lookup control. This configuration becomes a simple but powerful tool for guiding user input and enforcing business policy. Out-of-the-box, CRM offers a few methods to achieve custom filtering through configuration and without coding. For example, you may want to filter available Facility/Equipment records for the Preferred Facility/Equipment selection on the Contact form. Here, we show only using the USD currency:
Another common scenario is to filter a lookup based on user input. For example, on the Contact form the Price List should be filtered based on the Contact’s currency. This is actually out of the box configuration:
No-Code Configuration Methods
Two options are available when filtering with configuration alone: predefined entity views or linking via “Related Records”.
The predefined entity view involves setting up custom entity views and attaching them to the selected lookup. The advantage of this is approach is that you can use predefined filters against existing metadata across the system, or across role based forms, with one configuration setting. In the example above, restricting Facility/Equipment selection to the those with USD as their currency is done by specifying the custom view on the “Additional Properties” section of the Preferred Facility/Equipment lookup control in the Contact form designer:
The custom view is defined with the following filter criteria on the Facility/Equipment entity:
In the Price List example, the “Related Records” method can be used to link two lookup controls on the form capturing the user’s input as they work on the record. In this case, the filtering will be applied in the “Related Records Filtering” section on the Price List lookup control on the Contact form. The advantage of this method over the predefined entity view method is the ability to use the user’s input and dynamically filtering the associated lookup rather than a system wide static filter.
Note: This method is limited to connecting lookup values only. You cannot apply a filter based on other attribute types such as Option Sets or Numbers.
Both methods outlined above are available out-of-the-box within the forms designer. Neither approach requires additional development effort or customizations to implement.
Alternative Methods Using Code
What if you need to filter based on attribute values outside configurable options? For example, you may want to filter based on user selection of an Option Set value or a string input. The predefined entity view method is out because it is a static configuration. The “Related Records” method allows filtering based on user input, but it’s restricted to lookups only.
Using addCustomView
An alternate approach requires development effort, but offers significant flexibility to meet business requirements. This approach uses the addCustomView() method on the lookup control. This method, part of the Xrm.Page object model, overrides the lookup filter by attaching a custom criteria either on load of the form or as the user makes a selection.
The lookup control passes this filter criteria to the filtered lookup dialog when launched. The method has the following parameters:
|
1 |
addCustomView(viewId, entityName, viewDisplayName, fetchXml, layoutXml, isDefault) |
- viewId – unique identifier of the new view. This will provide a unique ID in the combo list of available Views. This can be any unique identifier value.
- entityName – The entity schema name bound to the lookup control.
- viewDisplayName – A displayable name of the view for the user in the views combo.
- fetchXML – The body of the FetchXML being applied to the lookup grid.
- layoutXML – The grid column layout settings for the related FetchXML.
- isDefault – Flag indicating whether the view should be the default view.
When this method is applied, the resulting behavior is very similar to applying preconfigured entity views described previously, however, the lookup dialog will be filtered using the fetchXML passed in.
Example
Let’s dive into an example to illustrate when and how to use the addNewCustomView() method. In this example, the business would like to filter the available User records when selecting Preferred User under the Service Preferences section of the Contact form. The new filter would restrict Users to those with the same Mailing Address State/Province as the Contact Address State/Province value.
The SDK example illustrates usage with long snippets of XML for the fetch and layout XML embedded within the javascript. To support reuse and maintainability, it is recommended that the above values be stored in an XML web resource rather than set within the JavaScript file. The XML web resource is loaded in at runtime and, if needed, dynamic values are replaced with values updated on the CRM form. Using this approach, the application of the custom filter would follow these steps:
- Register for change event
- Load correct XML web resource
- Update fetchXML with user specified values
- Apply custom filter to corresponding lookup
The first step in this process is to create a custom javascript web resource containing the State/Province event handler and attach it (and supporting scripts) to the Contact form:
The second step is to load the correct resource. Instead of hard coding this in a JavaScript web resource, the values can be stored in an XML web resource template (outlined below). Notice that the template below has a similar node structure to the parameters that are required in the addCustomView() method.
Note: the fetchXML and layoutXML parameter names have been changed to “fetch” and “grid” in the template since these are elements expected by the addCustomView():
|
1 2 3 4 5 6 7 8 |
<filteredLookup> <viewId /> <entityName /> <viewDisplayName /> <fetch /> <grid /> <isDefault /> </filteredLookup> |
Below is an example of an XML web resource that will be saved and retrieved within the Contact form events. The viewId, entityName, viewDisplayName parameters are populated while the fetch and grid elements contain full XML elements. The fetch will return system users filtered by a State/Province value as specified in the Contact address fields. The filter in the fetch is indicated by the {state} placeholder slug.
The XML needs to support some variable replacement, so slugs are used instead of actual filtering values. The slugs can be replaced using the JavaScript string replace method upon retrieval. To follow similar formats within CRM as in workflows and email templates, we formatted slugs using the curly braces: {variable}
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
<?xml version=“1.0” encoding=“utf-8” ?> <filteredLookup> <viewId>{5F4211B8-10A1-4895-9A3C-617D2EBF2150}</viewId> <entityName>systemuser</entityName> <viewDisplayName>Users Filtered by State</viewDisplayName> <isDefault>true</isDefault> <fetch version=“1.0” output-format=“xml-platform” mapping=“logical” distinct=“false”> <entity name=“systemuser”> <attribute name=“fullname” /> <attribute name=“title” /> <attribute name=“address1_telephone1” /> <attribute name=“businessunitid” /> <attribute name=“siteid” /> <attribute name=“systemuserid” /> <order attribute=“fullname” descending=“false” /> <filter type=“and”> <condition attribute=“isdisabled” operator=“eq” value=“0” /> <condition attribute=“accessmode” operator=“ne” value=“3” /> <condition attribute=“address1_stateorprovince” operator=“eq” value=“{state}” /> </filter> </entity> </fetch> <grid name=“resultset” object=“1” jump=“fullname” select=“1” icon=“1” preview=“1”> <row name=“result” id=“systemuserid”> <cell name=“fullname” width=“300” /> <cell name=“siteid” width=“150” /> <cell name=“businessunitid” width=“150” /> <cell name=“title” width=“150” /> <cell name=“address1_telephone1” width=“150” /> <cell name=“address1_stateorprovince” width=“150” /> </row> </grid> </filteredLookup> |
To retrieve the XML web resource from the server, we recommend using JQuery ajax methods. The JQuery library will return a simple XML object that can be easily parsed:
|
1 2 |
var xmlUrl = Xrm.Page.context.prependOrgName(“/WebResources/” + resourceName); $.ajax({ url: xmlUrl, data: null, async: true, success: SuccessCallBack, error: AjaxErrorCallback, dataType: “xml” }); |
Once executed and the XML resource content values are returned, these values can be when invoking the addCustomView() method. If the fetchXML contains variable slugs, these values will need to be replaced the correct dynamic values. Once all of the web resource contents have been processed, the values are passed to the addCustomView() method on the control:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
function SuccessCallBack (xml) { var params = [“state”, Xrm.Page.getAttribute(“address1_stateorprovince”).getValue()]; var grid = Celedon.Utility.XMLToString($(xml).find(“grid”)[0]); var viewId = $(xml).find(“viewId”).text(); var entityName = $(xml).find(“entityName”).text(); var viewDisplayName = $(xml).find(“viewDisplayName”).text(); var fetchXml = Celedon.Utility.XMLToString($(xml).find(“fetch”)[0]); var isDefault= ($(xml).find(“isDefault”).text() == “true”); fetchXml = Celedon.Utility.ReplaceParamSlugs(fetchXml, params); var controlObject = Xrm.Page.getControl(“preferredsystemuserid”); controlObject.addCustomView(viewId, entityName, viewDisplayName, fetchXml, layoutXml, isDefault); controlObject.setDefaultView(viewId); } |
This example will now apply the state filter when the user changes the State/Province on the Contact form. When selecting the Preferred User, the user will now have a new view in the list and the users list will be filtered by the entered state.
This could easily be extended to run on load of the form, such as when opening an existing contact. Also, the above script snippets use helper methods for handling the XML and replacing variable slugs. Within the downloadable example solution, you can see several helper methods for the callbacks, building parameters, replacing slugs, etc. All will add to reusability and maintenance and could all be extended and refined for your specific use case. For example, you could create a reusable callback method in the web resources library that further wraps the method calls to apply the custom filters, further reducing the amount of code to write each time this is used.
Summary
Microsoft Dynamics CRM filtered lookups are a very powerful business tool. In this post we outline several approaches for creating filtered lookups. The first two options are delivered with some simple configuration, starting with a system wide filter and narrowing to a filter based on user input. The third option outlined is for a more granular level of control at the scripting level, dynamically attaching custom filter criteria based on user selection. Additionally, our recommendation as a best practice was outlined offering reuse across multiple forms and / or similar scenarios. This web resource based approach reduces redundancy within the code, decreases the risk for potential errors, and simplifies the maintenance of the solution files.
If you have questions or comments on this post, please let us know!
Download the sample solution file here.
We are releasing this under as Open Source under the Microsoft Public License. You can read the details of that here. This software is provided “AS IS” with no warranties and confers no rights.