Sunday 24 August 2014

CRM 2013 Using addCustomFilter() to get Filtered Lookup Field based on Linked Entity

We all have known that in CRM 2013 we can filter the lookup field using easier method compared to CRM 2011, that is using addCustomFilter() combined with addPreSearch() function.
This is one of the example how to use it, to filter contact based on email that contains @example.com
function onLoad()
{
    addEventHandler();
}

function addEventHandler() {
    // add the event handler for PreSearch Event
    Xrm.Page.getControl("parentcontactid").addPreSearch(addFilter);
}

function addFilter() {
   //find contact contains this @example.com
   var email = "%@example.com%";

    //create a filter xml
   var filter = "<filter type='and'>" +
                "<condition attribute='emailaddress1' operator='like' value='" + email + "'/>" +
                 "</filter>";

    //add filter
    Xrm.Page.getControl("parentcontactid").addCustomFilter(filter);
}

Call the onLoad function during form onLoad event.

That code is looks good and it will work, yes, it will.

And if you notice, this is a very simple way, compared to the old code, using addCustomView(), which you need to build your own custom view and don’t forget to build layoutXML as well.

This is the addCustomView().
Xrm.Page.getControl(arg).addCustomView(viewId, entityName, viewDisplayName, fetchXml, layoutXml, isDefault)

However, there is a limitation, not only that is because those functions can only work for lookup field in the Refreshed UI Entity Form (it means cannot work for Price List Item, Quote Product, etc.), but this filter, if you notice, it can only work to filter using the attribute that is a part of that entity. So, it cannot be used to filter an entity based on the field from another entity.

The above example is to filter contact by email address (email address is one of the contact attribute), what if we are going to filter an entity record based on another related entity, for example in the Lead form, it has Existing Contact lookup field, then the users want to filter to show only Contact based on the Account Number or based on the Website/Email Address of Account or based on the Account Name that compared to the obtained Company Name in Lead form .

If you notice, the filter that is supported is currently just using the node that is started from <filter>..

//create a filter xml
    var filter = "<filter type='and'>" +
                 "<condition attribute='emailaddress1' operator='like' value='" + email + "'/>" +
                 "</filter>";

However, from that XML we get (see the example below), if we are using another related entity as the filter, it is using <linked-entity>, which is not allowed in the addCustomFilter() method.

This is the example of the generated XML with criteria based on the another related entity’s field:

*The XML to get Contact based on Account’s email address.
.
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
 <entity name="contact">
   <attribute name="fullname" />
   <attribute name="parentcustomerid" />
   <attribute name="telephone1" />
   <attribute name="emailaddress1" />
   <attribute name="contactid" />
   <order attribute="telephone1" descending="false" />
   <link-entity name="account" from="primarycontactid" to="contactid" alias="ab">
     <filter type="and">
       <condition attribute="emailaddress1" operator="like" value="%example%" />
     </filter>
   </link-entity>
 </entity>
</fetch>

So…

We cannot use these lines as variable filter.
<link-entity name="account" from="primarycontactid" to="contactid" alias="ab">
     <filter type="and">
       <condition attribute="emailaddress1" operator="like" value="%example%" />
     </filter>
</link-entity>

As we know that the only way is still using the old-fashioned CRM 2011 Code, that is using addCustomView() with many parameters that you might not want to build anymore.

Xrm.Page.getControl(arg).addCustomView(viewId, entityName, viewDisplayName, fetchXml, layoutXml, isDefault)

So, we should build our own fetchXML + layoutXML as well, in fact you have built your own view and you just need to filter it.

Then, as the alternative, you can use another code to find that related entity id, see this example:

var filter = "<filter type='and'>" +
             "<condition attribute='parentcustomerid' operator='eq' uiname='Fourth Coffee (sample)' uitype='account' value='{DBBB8E56-B9D9-E311-9410-001CC4EECDD6}' />" +
             "</filter>";

    //add filter
Xrm.Page.getControl("parentcontactid").addCustomFilter(filter);

You can use OData Query to find the Account Id.

The best part that you should do is by getting the AccountId and I believe the OData Query is will be your best friend.
To get used using odata, you can refer to my another blog post:

http://missdynamicscrm.blogspot.com/2014/10/tips-and-trick-odata-crm-2011-2013.html

Another limitation in addCustomFilter() is it only supports 'And'
(based on this link:http://msdn.microsoft.com/en-us/library/gg334266.aspx#BKMK_addCustomFilter)

If you need more than one Account Id, then you might need to add Operator IN, see this example:
<filter type="and">
        <condition attribute="accountid" operator="in">
          <value uiname="Fourth Coffee (sample)" uitype="account">{DBBB8E56-B9D9-E311-9410-001CC4EECDD6}</value>
          <value uiname="Litware, Inc. (sample)" uitype="account">{DDBB8E56-B9D9-E311-9410-001CC4EECDD6}</value>
          <value uiname="Adventure Works (sample)" uitype="account">{DFBB8E56-B9D9-E311-9410-001CC4EECDD6}</value>
        </condition>
</filter>

You might choose whether you want to use addCustomView() with those many required parameters or addCustomFilter() with that limitation and have to find the ID, but with no need to pass many parameters as the addCustomView() required.

Summary:

1. To use addCustomFilter() to filter based on the same entity field:
function addFilter() {
   //find contact contains this @example.com
   var email = "%@example.com%";

    //create a filter xml
    var filter = "<filter type='and'>" +
                 "<condition attribute='emailaddress1' operator='like' value='" + email + "'/>" +
                 "</filter>";

    //add filter
    Xrm.Page.getControl("parentcontactid").addCustomFilter(filter);
}

2. To filter based on the related entity (one record only):

 //create a filter xml
 var filter = 
"<filter type='and'>" +
     "<condition attribute='parentcustomerid' operator='eq' uiname='Fourth Coffee” (sample)' uitype='account' value='{DBBB8E56-B9D9-E311-9410-001CC4EECDD6}' />" +
"</filter>";

3. To filter based on the related entity (multiple records)

//create a filter xml 
var filter =
"<filter type='and'>" +
        "<condition attribute='accountid' operator='in'>" +
          "<value uiname='Fourth Coffee (sample)' uitype='account'>{DBBB8E56-B9D9-E311-9410-001CC4EECDD6}</value>" +
          "<value uiname='Litware, Inc. (sample)' uitype='account'>{DDBB8E56-B9D9-E311-9410-001CC4EECDD6}</value>" +
          "<value uiname='Adventure Works (sample)' uitype='account'>{DFBB8E56-B9D9-E311-9410-001CC4EECDD6}</value>" +
        "</condition>" +
"</filter>";
//or you can just use this (uiname and uitype are not mandatory)

//create a filter xml
 var filter =
"<filter type='and'>" +
        "<condition attribute='accountid' operator='in'>" +
          "<value>{DBBB8E56-B9D9-E311-9410-001CC4EECDD6}</value>" +
          "<value>{DDBB8E56-B9D9-E311-9410-001CC4EECDD6}</value>" +
          "<value>{DFBB8E56-B9D9-E311-9410-001CC4EECDD6}</value>" +
        "</condition>" +
"</filter>";


Result for Number 2 (Single Record):

customfilter single value

Result for Number 3 (Multiple Records):

customfilter multiple value

*Using the addCustomFilter() has advantage it will not break the column order and position you defined in the Customization, because you are just changing the criteria to filter, not creating a new custom view, so that you will not find any issue like I found and posted here.


Hope this helps!

Thanks.

38 comments:

  1. Hi Alieen!

    I'm trying to set up an In filter to a view, this is not working when is set up to a Team lookup. Do you know which could be the problem here?

    "{d2f2af7c-9c2e-e411-a53a-00155d5248a4}{cff2af7c-9c2e-e411-a53a-00155d5248a4}"

    ReplyDelete
  2. filter type= "and"
    condition attribute="businessunitid" operator="in"
    value uitype="team" {d2f2af7c-9c2e-e411-a53a-00155d5248a4}
    value
    value uitype="team"
    {cff2af7c-9c2e-e411-a53a-00155d5248a4}
    value
    condition
    filter

    ReplyDelete
    Replies
    1. Hi Jorge,

      Have you change the " to '?
      What does error message you get?

      Delete
  3. Hi Aileen,
    I've to filter Team Lookup based on user id((Link Entity).I followed the same which u suggested .
    but getting error as Specified field not available in CRM.
    Here is my Filter.
    var filter =
    "" +
    "" +
    "";

    ReplyDelete
    Replies
    1. Hi Hareesh,

      What is your filter (complete filter)? I try to help you.

      Thanks.

      Delete
    2. Hi Alieen,
      Thanks for the Response.
      I've Lookeup field named team in which i've to filter the team in which the Logged in user belongs to .
      my filter uses linkentities Team==>teammembership==>ystemuser

      filter type='and'
      condition attribute='systemuserid' operator='eq' uitype=systemuser' value='" + User + "'
      filter

      user field is the logged in user ID

      Delete
    3. Hi Hareesh,

      In the Team entity, it does not have 'systemuderid' field.
      You need to do nested filter to get the teammbershipid, not the systemuser id, because the fetch XML that you will use is like this:

      filter type='and'
      condition attribute='systemuserid' operator='eq' uitype=systemuser' value='" + User + "'
      filter


      Which is not supported in addCustomFilter (limitation).

      Instead, you need to get the teamid, which you get from System User -> Team Membership --> Team, get the Team Id.

      The Team and System User relationship is N:N Relationship which is not supported.
      You need to use this filter XML:

      filter type='and'
      condition attribute='teamid' operator='eq' uitype=team' value='" + TeamId+ "'
      filter

      Which the TeamId you need to get from System User.

      See this link to get javascript code connecting Team and User

      http://www.dynamicscrmpros.com/checking-user-member-team-using-javascript/

      Later after you get the teamId, instead using the linked entity (N:N is not supported), you use this filter:

      //create a filter xml
      var filter =
      filter type='and' +
      condition attribute='teamid' operator='eq' value='{DBBB8E56-B9D9-E311-9410-001CC4EECDD6}' +
      /filter

      //assuming 'DBBB8E56-B9D9-E311-9410-001CC4EECDD6' is your teamId you got from previous steps.

      //sorry I think the comment does not support some characters

      Hope this helps, Hareesh.

      Delete
  4. But as soon as you refresh the lookup view in the dialog, the filter is gone. Have you noticed that?

    ReplyDelete
  5. Is it possible to bind custom view at 'on change' event of other field

    ReplyDelete
  6. "Another limitation in addCustomFilter() is it only supports 'And'."
    This is not entirely true, you can use 'Or' as well, but you have to use multiple filter nodes.

    example:
    filter type='and'>
    filter type='and'>
    condition attribute='country' operator='eq' value='US'/>
    /filter>
    filter type='or'>
    condition attribute='city' operator='like' value='L%'/>
    condition attribute='city' operator='eq' value='Oxford'/>
    /filter>
    /filter>

    This filter would show all records with country US and also additional records with cities starting with L and Oxford.

    ...you get the idea :)

    ReplyDelete
    Replies
    1. Hi,

      Yes, thanks for the comment, you need to do more effort to do that. The AND one is based on MSDN Article.
      I think as long as still in the same entity context, the filter And Or will work also, the AND one is I got from MSDN one.

      Delete
  7. Hi Aileen,
    I am trying to do the same thing and the suggestion you provided to someone about the current user's team filtering in the comments.

    Here is my script which I tried from your suggestion. But the script itself is not getting triggered on form load.
    Could you please take a look at it?
    Thanks.

    function getUserTeam()
    {
    debugger;
    addEventHandler();
    }

    function addEventHandler() {
    // add the event handler for PreSearch Event
    Xrm.Page.getControl("new_userteam").addPreSearch(addFilter()
    {
    var TeamId= getTeamId();

    //create a filter xml
    var filter = "";

    //add filter
    Xrm.Page.getControl("new_userteam").addCustomFilter(filter);

    });
    }


    function getTeamId(){
    var myTeamId = null;
    var myTeamName = null;

    var guid = "[A-z0-9]{8}-[A-z0-9]{4}-[A-z0-9]{4}-[A-z0-9]{4}-[A-z0-9]{12}";

    var serverUrl = Xrm.Page.context.getClientUrl();
    var userId = Xrm.Page.context.getUserId();
    userId = userId.match(guid);
    userId = userId.input;

    // Retrieve TeamIds of the Teams Current SystemUser belongs to
    var userTeamsQuery = "TeamMembershipSet?$select=TeamId&$filter=SystemUserId eq guid'"+ userId +"'";
    var userTeams = makeRequest(userTeamsQuery);

    var teamQuery = null;
    var teams = null;

    if(userTeams !== null){

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

    userTeams[i] = userTeams[i].TeamId;

    //console.log("Team Id : " + userTeams[i]); // to see TeamIds of user in Console

    var teamId = "{" + userTeams[i] + "}";
    return teamId;
    }
    }
    }

    function makeRequest(query) {
    var serverUrl = Xrm.Page.context.getClientUrl();

    var oDataEndpointUrl = serverUrl + "/XRMServices/2011/OrganizationData.svc/";
    oDataEndpointUrl += query;

    var service = GetRequestObject();

    if (service != null) {
    service.open("GET", oDataEndpointUrl, false);
    service.setRequestHeader("X-Requested-With", "XMLHttpRequest");
    service.setRequestHeader("Accept", "application/json, text/javascript, */*");
    service.send(null);

    var retrieved = JSON.parse(service.responseText).d;

    var results = new Array();
    for (var i = 0; i < retrieved.results.length; i++) {
    results.push(retrieved.results[i]);
    }

    return results;
    }
    return null;
    }


    function GetRequestObject() {
    if (window.XMLHttpRequest) {
    return new window.XMLHttpRequest;
    } else {
    try {
    return new ActiveXObject("MSXML2.XMLHTTP.3.0");
    } catch (ex) {
    return null;
    }
    }
    }

    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete
  9. I have follow your blog that's very nice follow us on :- Best CRM Software and online lead management

    ReplyDelete
  10. Hi Aileen - very informative post. Can you help me figure out how I would go about filtering a contact selector so that it only showed the contacts who had a specific connection role to an account?

    ReplyDelete
  11. This comment has been removed by the author.

    ReplyDelete
  12. Great Post and successfully installed it in version 2015 Upgrade 1. Since upgrade to version 2016 the code below the .addPreSearch is not getting executed, therefore the filter is not being applied. Any ideas on how to correct this. No error is being received. Below is code

    function preFilterDepartmentLookup()
    {
    debugger;
    Xrm.Page.getControl("vbs_ruledepartmentid").addPreSearch(function () {

    var departmentObject = Xrm.Page.getAttribute("vbs_ruledepartmentid").getValue();

    // Get the lookup Object, and extract the Value (ID and Text)
    if(departmentObject != null){
    var departmentTextValue = departmentObject[0].name;
    var departmentID = departmentObject[0].id;

    // Filter the Categories by Department
    var fetchXml = "" +
    " " +
    " " +
    " " +
    " " +
    " " +
    "";

    // Apply the filter to the field
    Xrm.Page.getControl("vbs_vbsrulecategory").addCustomFilter(fetchXml);
    }

    });
    }

    ReplyDelete
  13. Hi Aileen,

    I have parent-child lookup functionality in one of my entity.

    On select of Parent lookup value, my child lookup will be prefiltered on the basis of Parent lookup.

    Now if user change value in Parent Lookup then Child lookup filter condition should update.

    filtLookup.addPreSearch(function () {
    fetchXml = "";

    Xrm.Page.getControl("childlookup").addCustomFilter(fetchXml);
    });

    I am using above code and everytime it is adding custom filter.

    Can you please let me know how can I remove all old filters and add new filter every time when user update parent lookup?

    Kindly waiting for your reply.

    Thanks,
    Mohit

    ReplyDelete

  14. شركة كشف تسربات المياه بالاحساء

    الموقع الرائد فى عالم الخدمات المنزليه والاول بالمملكه العربيه السعوديه لما يتمتع به من خدمات مميزه ، فالبرغم من اننا مؤسسه ربحيه الا ان مزاولة نشاطتنا كلها مرتبط على نحو وثيق بتلبية طلبات وحاجات عملائنا ولتحقيق ذلك الهدف نقدم لك كافة الخدمات الشامله بالالتزام الصارم وبأرقى المعايير المهنيه المتطوره
    فلدينا خبره طويله فى مجال التنظيف ومكافحة الحشرات والكشف عن التسربات وتسليك المجارى وعزل الاسطح ونقل الاثاث وتخزينه بكفاءة منقطعة النظير ، لا تتردد واتصل بموقع مؤسسة الحرمــين فخدماتنا ليس لها بديل واسعارنا ليس لها مثيل ،ولدينا فريق عمل يتصل مع العملاء على جسور الثقه والصدق والامانه فى العمل ، وهدفنا هو ارضاؤك وراحتك ، لا تقلق ونحن معك .. لا تجهد نفسك ونحن تحت امرك ورهن اشارتك .
    أبرز خدمات مؤسسة الحرمــين
    شركة كشف تسربات المياه بالجبيل

    كشف تسربات المياه بالجبيل


    شركة عزل اسطح بالاحساء


    شركة عزل اسطح بالاحساء

    ReplyDelete

  15. Nice article really informative for me as on this I’ve found some best results for
    SuiteCRM Customization
    As this found the best services for crm integration
    SugarCRM integration Services
    CRM Integration

    ReplyDelete
  16. Hi,
    Pretty good article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing.
    thank you,
    Oracle Financials training

    ReplyDelete
  17. Excellent article...to make movies visit Rave Films Production

    Corporate Film Production || Best Corporate Video || Ad Film Production

    ReplyDelete
  18. Thanks for the information and links you shared this is so should be a useful and quite informative!
    Filter Operator

    ReplyDelete
  19. I have read your blog its very attractive and impressive. I like your blog
    salesforce Online course Bangalore

    ReplyDelete
  20. I read that Post and got it fine and informative.
    malaysia best crm

    ReplyDelete
  21. Thanks for helping us understand this topic. You have written it in a way that makes it very simple to understand. Thank you so much.
    Customized CRM Solutions
    Customer Relationship Management Solutions in Bangalore

    ReplyDelete
  22. Good Information Thanks for Sharing
    Soft Online Provides best online training for Oracle Fusion and EBS R12 Courses
    Oracle EBS Training in Hyderabad
    Oracle Fusion SCM Training in Hyderabad
    Oracle Fusion HCM Training
    Oracle Fusion Financials Training
    For more info Visit us: https://www.softonlinetraining.com/

    ReplyDelete
  23. Tourcrms company provides the best Customer Relationship Management Software or crm for tour operators for tour agencies and tour operators. Improve your customer retention & profits. Once a customer makes a booking with you, they are automatically saved in the tour operator CRM so you can reference their information whenever you need. For more details: http://tourcrms.com.

    ReplyDelete
  24. I'M NOW FULFILL FINANCIALLY BECAUSE OF THE LOAN I GOT FROM LFDS .I would like to bring this to the notice of the public about how i came in contact with LFDS after i lost my job and being denied loan by my bank and other financial institution due to my credit score. I could not pay my children's fees. I was behind on bills, about to be thrown out of the house due to my inability to pay my rent, It was during this period my kids were taken from me by foster care. Then i set out to seek for funds online where i lost $3,670 that i borrowed from friends which i was rip off by two online loan companies. Until i read about:Le_Meridian Funding Service  (lfdsloans@outlook.com / lfdsloans@lemeridianfds.com) somewhere on the internet, Still wasn't convince because of what i have been through until a relative of mine who is a clergy also told me about the ongoing loan scheme of LFDS at a very low interest rate of 1.9%% and lovely repayment terms without penalty for default of payment. I have no choice than to also contact them which i did through text +1-989-394-3740 and Mr Benjamin responded back to me That day was the I'M best and greatest day of my life which can never be forgotten when i receive a credit alert of $400,000.00 Usd loan amount i applied for. I utilized the loan effectively to pay up my debts and to start up a business and today i and my kids are so happy and fulfill. You can as well contact them through email: (lfdsloans@outlook.com / lfdsloans@lemeridianfds.com)  WhatsApptext  helpline: +1-989-394-3740 Why am i doing this? I am doing this to save as many that are in need of a loan not to be victim of scams on the internet. Thanks and God bless you all, I'm Oleksander Artem from Horizon Park BC , Ukrain.

    ReplyDelete
  25. We are offering Best AWS cloud practitioner course Online. Build your cloud skills with updated modules, instructor led live online courses & certifications. Enroll today.
    https://onlineidealab.com/aws-course/

    ReplyDelete
  26. This seems to be the age of the entrepreneur, with small startups such as Facebook and Twitter proving that small businesses can grow - potentially exponentially with the right resources. Unfortunately, success stories like Facebook are rare in the small business world, with over half of small businesses failing within five years of their startup, mainly due to lack of funding. As any small business or entrepreneur knows, funding is one of the most difficult parts of starting a... Nicolas Krafft L'Oreal

    ReplyDelete
  27. I got what you mean , saved to fav, very decent website . click site

    ReplyDelete

My Name is..