Apply Custom Filter on Lookup Field in Dynamics CRM using Script

By | August 20, 2015

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;Apply Custom Filter on Lookup Field in Dynamics CRM using Script

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;Apply Custom Filter on Lookup Field in Dynamics CRM using Script

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.Apply Custom Filter on Lookup Field in Dynamics CRM using Script

Function ‘filterLookup’ will be triggered on the change of field ‘Address1: City’. This binds ‘addPreSearch’ event to lookup control ‘parentcustomerid’.Apply Custom Filter on Lookup Field in Dynamics CRM using Script

Open the contact entity record, Before entering the ‘Address1:City’ field value the lookup field shows all the account records as below screenshot;Apply Custom Filter on Lookup Field in Dynamics CRM using Script

Enter the value for ‘Address1: City’ here it is ‘US’ as below;Apply Custom Filter on Lookup Field in Dynamics CRM using Script

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.

18 thoughts on “Apply Custom Filter on Lookup Field in Dynamics CRM using Script

  1. tdailey77

    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

  2. Madhan

    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?

    1. Inogic

      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.

  3. Ankita

    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

    1. Inogic

      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!

  4. Raj

    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);
    }
    }

    1. Inogic

      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!

      1. Raj

        Can you share the fetchXML script, its blank and not able to post the FetchXML.

        1. Inogic

          Here is the complete code:
          var fetchXML = “<filter type=’and’><condition attribute=’emailaddress1′ operator=’not-null’ /></filter>”;
          Xrm.page.getControl(“primarycontactid”),addCustomFilter(fetchXML);

          Thanks!

  5. Shelton

    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);
    }
    }

    1. Inogic

      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!

  6. Paul

    Hi great article. How can I apply a filter that gives distinct values of the lookup table?

    1. Inogic

      No, we cannot use filter to give distinct values in lookup table.

      Thanks!

Comments are closed.