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
    Replies
    1. No, it's still there as long as I notice

      Delete
  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. Wow! Great post! The content is very rich, and I really like it. It help me very much to solve some problems. It is very helpful for all the people on the web. Thanks a lot. Ecommerce Product Photography || Ecommerce Product Photoshoot || Products Catalogue

    ReplyDelete
  8. Wow! Great post! The content is very rich, and I really like it. It help me very much to solve some problems. It is very helpful for all the people on the web. Thanks a lot. Ecommerce Product Photography || Ecommerce Product Photoshoot || Products Catalogue

    ReplyDelete
  9. 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
  10. This comment has been removed by the author.

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

    ReplyDelete
  12. FetchXML you can here online http://msxrmtools.com

    ReplyDelete
  13. 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
  14. This comment has been removed by the author.

    ReplyDelete
  15. 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
  16. 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

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

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

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


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


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

    ReplyDelete

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

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

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


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


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

    ReplyDelete

  19. 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
  20. 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
  21. Excellent article...to make movies visit Rave Films Production

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

    ReplyDelete

My Name is..