Introduction:
In this blog, we are going to see how to apply a custom filter to the lookup field using the JavaScript functions.
Microsoft Dynamics CRM allows us to filter a lookup field on the form using the Fetch XML condition and “addPreSearch()“ method.
Example:
On the Contact Entity, there is a lookup field named ‘Account Name’ and a text field ‘Address1: City’ as shown in the below screenshot;
So, if we want to filter Account records in lookup view by city having value equal to field Address1: City. We can do this by writing the below code in JScript.
Here we have written two functions ‘filterLookup()’ and ‘addCustomeLookupFilter()’ as shown in the below code snippets;
We have created CRM webresource for the javascript and called ‘filterLookup’ function on change event for the field ‘Address1: City’ field as below for contact entity form.
Function ‘filterLookup’ will be triggered on the change of field ‘Address1: City’. This binds ‘addPreSearch’ event to lookup control ‘parentcustomerid’.
Open the contact entity record, Before entering the ‘Address1:City’ field value the lookup field shows all the account records as below screenshot;
Enter the value for ‘Address1: City’ here it is ‘US’ as below;
Then Check for the suggested options for the Account lookup. Only those accounts records will be available to select which have the city as ‘US’.
Hope this helps!
Free 70% of storage space in CRM with Attachment Management Apps!
Attach2Dynamics – Store and manage documents/attachments in cloud storage of your choice – SharePoint, Dropbox or Azure Blob Storage from within Dynamics 365 CRM.
SharePoint Security Sync – Robust and secure solution to integrate Dynamics 365 CRM and SharePoint Security Sync thereby ensuring secure access to confidential documents stored in SharePoint.
Hello,
Can you help me with the same but on Phone Call form.
The “Call To” field – we would like to filter that lookup to only show contacts that work for the Account that is in the “regarding” of the Phone Call record. For example, I go to Account ABC and add a phone call activity. The “Call To” lookup when I click on the magnifying glass should only show contacts that work for Account ABC. Possible? Can you show me the code and process to achieve this? Thank you
Hi Tim,
The To field is Party list so the filter will not be apply on To field.
You can achieve this by setting custom view for the To field.
Please visit the below link for reference.
https://www.inogic.com/blog/2014/09/add-custom-view-in-lookup-and-disable-all-the-other-views/
Thanks,
Sam
if you want to add filter dynamically using code , then you can check this post.
https://dynamicsforbusiness.blogspot.com/2018/10/dynamics-crm-native-lookup-with-filtered-view.html
Hello,
I have a similar kind of requirement. I have added these pieces of code and it is getting triggered. But I am not getting the expected records in the lookup. When I execute that in debugger mode, it’s showing “Undefined” for Xrm.Page.getControl(mylookupfield).addCustomFilter(fetchXml). Am I missing something?
Hi,
We have checked this on our end (i.e. on CRM Online) and it is working as expected. If you still face the same issue, then try using “formcontext” as mentioned below.
Use “formcontext.getControl()” method instead of the “Xrm.Page.getControl()” method in order to retrieve lookup control and then add “Custom Filter” on it.
Please refer below code-snippet,
var formcontext = executioncontext.getFormContext();
//filter products based on Asset Category
formcontext.getControl(“msdyn_product”).addPreSearch(function () {
var fechxml = “ “;
formcontext.getControl(“msdyn_product”).addCustomFilter(fechxml);
});
If you still face this issue, then please provide the “fetchXML” filter condition and CRM version, so that we can check further.
Kindly check and let me know if there are any queries regarding the same.
Thank you! You saved me! 🙂
Hi ,
I have to apply custom filter to a lookup field, the scenario is
I have a custom entity , where there is two lookups. One is Account : value will be populatged by default , now what i want is when the account selected is parent account, then for second lookup of Opportunities I need to show opportunities of Parent as well as child accounts of this Parent Account to be able to come in lookup. can you please help me.
Thanks
To filter Opportunities based on selected parent account and its child accounts, you need to apply filter using link entity (i.e. Account) and you can achieve this by using “addCustomView()” function instead “addCustomFilter()” function.
Also, you can get Opportunity records using below FetchXml, just pass your selected Account instead “Parent Account 1”.
FetchXML:
<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”false”>
<entity name=”opportunity”>
<attribute name=”name” />
<attribute name=”customerid” />
<attribute name=”estimatedvalue” />
<attribute name=”statuscode” />
<attribute name=”opportunityid” />
<order attribute=”name” descending=”false” />
<link-entity name=”account” from=”accountid” to=”parentaccountid” link-type=”inner” alias=”ar”>
<filter type=”and”>
<filter type=”or”>
<condition attribute=”accountid” operator=”eq” uiname=”Parent Account 1″ uitype=”account” value=”{0D55D9C6-A49A-EA11-A811-000D3A192311}” />
<condition attribute=”parentaccountid” operator=”eq” uiname=”Parent Account 1″ uitype=”account” value=”{0D55D9C6-A49A-EA11-A811-000D3A192311}” />
</filter>
</filter>
</link-entity>
</entity>
</fetch>
You can use below link for reference,
https://docs.microsoft.com/en-us/powerapps/developer/model-driven-apps/clientapi/reference/controls/addcustomview
Also, you can refer our below blog for the same.
Blog Link: https://www.inogic.com/blog/2014/09/add-custom-view-in-lookup-and-disable-all-the-other-views/
Hope this helps.
Thanks!
Hi Inogic,
i need to make a lookup filter here is my scenario
Table 1:
Company Name
Test
Demo
Table 2:
PO and Company (Lookup from Table 1) – search is enabled for both columns and lookup view contains both columns
PO Child Company
—– ———————-
123 Demo
456 Test
Table 3:
JobNumber(Lookup ), PO (Lookup)
Looking Solution for
1) Not able to get the Lookup Column JobNumber Value.
2) HardCoded Value returns error
i tried the hardcoded value on the filter condition and here is my script
function filterlookup(){
try
{
alert(“HI”);
if(Xrm.Page.getControl(“JobNumber”) != null && Xrm.Page.getControl(“JobNumber”) != undefined)
{
Xrm.Page.getControl(“PO”).addPreSearch(function (){
addCustomelookupFilter();
});
}
}
catch (e)
{
throw new Error(e.Message);
}
}
function addCustomelookupFilter() {
var functionName = “addCustomelookupFilter”;
try
{
var RecordId = Xrm.Page.data.entity.getId();
var City = Xrm.Page.getAttribute(“JobNumber”).getValue();
if(City != null && City != undefined)
{
fetchXml = “”;
Xrm.Page.getControl(“PO”).addCustomFilter(fetchXML);
}
}
catch (e)
{
throw new Error(e.Message);
}
}
Basically, when you want to apply filter on lookup field you can achieve this by passing appropriate FetchXML filter to ‘addCustomFilter()’ function and it will filter the lookup field data on basis of the given FetchXML.
Note: In your script we observed that the fetchXML is empty.
For e.g. Suppose, we want to filter Primary Contact field on Account which will filter contacts whose email field contains data. For this, we need to add the below FextXML and need to pass this to ‘addCustomFilter()’ function.
var fetchXML = “<filter type=’and’><condition attribute=’emailaddress1′ operator=’not-null’ /></filter>”;
Xrm.page.getControl(“primarycontactid”),addCustomFilter(fetchXML);
Hope this helps.
Thanks!
Can you share the fetchXML script, its blank and not able to post the FetchXML.
Here is the complete code:
var fetchXML = “<filter type=’and’><condition attribute=’emailaddress1′ operator=’not-null’ /></filter>”;
Xrm.page.getControl(“primarycontactid”),addCustomFilter(fetchXML);
Thanks!
Thanks 🙂
Hi,
I have a similar requirement, but based on a drop down field to trigger the filtering. I have tried multiple methods and not receiving any errors but its also not filtering the data either. I am not sure what I am missing
var Sdk = window.Sdk || {};
// set ‘Sdk.setServiceFilter’ in the Case form onchange event handler region field
Sdk.setServiceFilter = function (executionContext) {
// get the form context
formContext = executionContext.getFormContext();
//get region field value
var region=formContext.getAttribute(“cssp_region”).getText();
Xrm.Page.getAttribute(“cssp_serviceregion”).setValue(region);
if (region != ‘global’) {
//Prepare Condition for the filter
Sdk.filterServices = function () {
// Only show services where region is like selected region
var ServicesFilter = “”;
formContext.getControl(“cssp_service”).addCustomFilter(ServicesFilter, “cssp_service”);
}
//Apply filter to the lookup field
formContext.getControl(“cssp_service”).addPreSearch(Sdk.filterServices);
}
}
As per your comment, you want to add a custom lookup filter onchange of drop down field.
You can refer below code to add a custom lookup filter.
Code :
var CRM;(function (CRM) {
var Lookup;
(function (Lookup) {
var Lib = /** @class */ (function () {
function Lib() {
}
Lib.prototype.setCustomLookupOnChangeRegion = function (executionContext) {
//declaring local variable
var functionName = "setCustomLookup";
var formContext = null;
try {
//Form Context
formContext = executionContext.getFormContext();
if (formContext.getControl("crm_region") != null && formContext.getControl("crm_region") != null) {
formContext.getControl("parentcustomerid").addPreSearch(this.addCustomLookupFilter);
}
}
catch (ex) {
console.log(functionName, ex);
}
};
Lib.prototype.addCustomLookupFilter = function (executionContext) {
//declaring local variable
var functionName = "addCustomLookupFilter";
var region = "";
var fetchXML = "";
var formContext = null;
try {
//Form Context
formContext = executionContext.getFormContext();
//Returns region
region = formContext.getAttribute("crm_region").getText();
console.log("Region:: " + region);
if (region != null && region != undefined) {
//Fetch Query
fetchXML = "
//Set Custom filter
formContext.getControl("parentcustomerid").addCustomFilter(fetchXML);
}
}
catch (ex) {
console.log(functionName, ex);
}
};
return Lib;
}());
Lookup.Lib = Lib;
})(Lookup = CRM.Lookup || (CRM.Lookup = {}));
})(CRM || (CRM = {}));
//initializing object
var _crmLib = new CRM.Lookup.Lib();
Hope this helps.
Thanks!
Hi great article. How can I apply a filter that gives distinct values of the lookup table?
No, we cannot use filter to give distinct values in lookup table.
Thanks!
Very helpfull