"тнιѕ вℓσg ¢συℓ∂ ѕανє уσυя мσηєу ιƒ тιмє = мσηєу" - ∂.мαηנαℓу

Friday 13 May 2011

Lookup Filtering in CRM 2011 using Javscript OR Custom Lookup Filtering in CRM 2011 using Javascript

Related Post : http://crmdm.blogspot.com/2011/04/lookup-fitering-feature-in-crm-2011-or.html

Let's see how we could configure custom lookup filter in CRM. Consider the following scenario.
We have a field called General Practitioner on the Account entity. We would like to place this field as a lookup of contacts. But the lookup of contacts should be filtered based on the profession field of Contact entity. So the lookup would be a list of doctors whose profession='Doctor'.

The contact records without filtering:


Also the contact form is shown below.




The main steps for the lookup filtering are the following.

Step 1: Build FetchXML
Step 2: Build Grid Layout
Step 3: Attach the new view to the lookup
So lets do it.

Step 1: Build FetchXML
As the first step of filtering we need to have fetchxml. But we don't need to worry about creation of FetchXML. MS Dynamics CRM will do it for you. But we need to decide 2 things
1) View of the Lookup
2) Filtering criteria
So navigate to Advanced Find and search for the contact records whose profession = 'Doctor'
Then you would get the following result.


We have defined a custom view with limited number of fields as shown below.


If we go back to Advanced Find tab, we could see a button called Download Fetch XML


Download the fetchXML to any local folder. The fetch XML file is shown below.


Step 2 & 3 should done in directly in the Js file. So we should call js function at the form load of Account. For your convenience, I have pasted the contents of the js file with proper comments.

Note: Do not forget to reformat the contents of FetchXML file as shown in the code below.
Step2 is the process of building a layout for the Lookup which is commented in the code (Its always ideal to keep same fields in the view used with the fetchXML and lookup View(except Id field). Remember the step Decide view while doing fetchXML). Also the step3 which is attaching the filterd lookup view to the lookup field General Practitioner is commented in the code.

function FilteredLookup() {
//Filter Condition--> Show Contacts which has got a Profession value as 'Doctor'
//Part 1 -->build fetchxml
var viewId = "{a76b2c46-c28e-4e5e-9ddf-951b71202c9d}"; //Any Guid is fine.
var entityName = "contact";// Entity to be filtered
var viewDisplayName = "Doctors"; // Custom name for the lookup window.
var fetchXml = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>" +
"<entity name='contact'>" +
"<attribute name='fullname' />"+
"<attribute name='contactid' />"+
"<attribute name='salutation' />"+
"<attribute name='new_profession' />"+
"<order attribute='fullname' descending='false' />"+
"<filter type='and'>"+
"<condition attribute='new_profession' operator='eq' value='100000001' />"+
"</filter>"+
"</entity>"+
"</fetch>";
// Part 2 --> Build Grid Layout. In other words, building a custom view for the Lookup
//building grid layout
var layoutXml = "<grid name='resultset' " +
"object='1' " +
"jump='name' " +
"select='1' " +
"icon='1' " +
"preview='1'>" +
"<row name='result' " +
"id='contactid'>" + // Id attribute of the entity to be filtered
"<cell name='salutation' " + //Column1 - Salutation
"width='300' />" +
"<cell name='fullname' " + //Column 2 - Fullname
"width='100' />" +
"<cell name='new_profession' " + //Column 3 - Profession
"width='100' />" +
"</row>" +
"</grid>";
 

 
//Part3 add new view to the lookup
Xrm.Page.getControl("new_generalpractitioner").addCustomView(viewId, entityName, viewDisplayName, fetchXml, layoutXml, true);
 

}


So here we go. As you could see only Doctors are listed in the lookup. The important point is that you could appy any filtering in the form of FetchXML. So I would say fetchXML is a cool feature in MS Dynamics CRM.



27 comments:

  1. Thanks for the great post...

    ReplyDelete
  2. thanks for this post. Just a quick question. In the call Xrm.Page.getControl("new_generalpractitioner")
    what does "new_generalpractitioner" refer to? To which control are we supposed to add the view.

    Thanks

    ReplyDelete
  3. Hi Toine,

    We need to add the view to the lookup control.

    Best Regards,

    ReplyDelete
  4. ok, but it's a must that "new_generalpractitioner" was referred to 'contact' entity type or it must be any one entity type.

    Thanks

    ReplyDelete
  5. it could be any entity and its just a scenario. Please note the line and change it accordingly
    var entityName = "contact";// Entity to be filtered

    ReplyDelete
  6. Sorry, I haven't been clear...
    In my scenario I'd like add custom view with customerid (incident entity) lookup what it's pointing at other entity (neither contact nor account).
    I'd like to add one attribute to my fetch query (linked-entity) it's in other entity. Both entities are related with 'customerid' field, but I can't access this attribute with advanced find query. For this reason I thought that add custom view could be the solution about it.
    I have achieved add custom view to my lookup, but I don't know if I can add an attribute from other entity or I do fetch query directly against 'custom_entity' with customerid field.
    Thanks

    ReplyDelete
  7. This is my function code. Now I don't get error, but it doesn't substitute the default account view. However, if I add custom_invoiceid lookup to my incident form, then It runs ok. I'm thinking to use this lookup to achieve my business logic and to change the customerid lookup value later.

    function setLookupCustomer(lookupFieldName)
    {
    // use randomly generated GUID Id for our new view
    var viewId = "{1DAC2B35-B07C-44D2-865D-258DEEAC88E2}";
    var entityName = "custom_invoice";

    // give the custom view a name
    var viewDisplayName = "Client";

    // Find all customers with their CIF (custom_invoice) entity
    var fetchXml = "" +
    "" +
    "" +
    "" +
    "" +
    "" +
    "" +
    "" +
    "" +
    "" +
    "";

    // build Grid Layout
    var layoutXML = "";
    layoutXML += "";
    layoutXML += "";
    layoutXML += "";
    layoutXML += "";
    layoutXML += "";
    layoutXML += "";

    // add the Custom View to the indicated [lookupFieldName] Control

    Xrm.Page.getControl(lookupFieldName).addCustomView(viewId, entityName, viewDisplayName, fetchXml, layoutXML, true);
    }

    ReplyDelete
  8. hello,
    thanks for this helpful post.
    but I am getting error 'FetchXml is not defined'
    and Object does not support this property or method
    any idea?

    Thanks!
    Sneha

    ReplyDelete
  9. Please verify the FetchXml format.Make sure that you download from Advanced find and then format as shown above. Also if possible, could you paste dummy code.

    ReplyDelete
  10. Can we display an attribute from linked Entity in the view? I am able to access the linked entity attribute in fetch XML, but couldn't add the cell in Lay out XML...Any idea??

    ReplyDelete
    Replies
    1. hello there, sorry for the delayed response. I haven't tried this. It may be a limitation. But what happens when you add the cell to the layout xml. Was there any error?

      Delete
  11. awesome code. thanx. almost got it t work first time, cept for changing all the " to ' . worked after that. bummer that the sort doesnt work though. still reported as an open bug I think.

    ReplyDelete
  12. Working like a charm, great blog.
    However, i am stuck in a situation:

    "How do i disable all the other views, i want to make sure that system only shows the view i have created, i understand we have to make sure that 'Show All views' property has to be TRUE but the downside is we/users can select any other view and that will reset the filters"

    What is the solution or its a bug?

    Will look forward to hear from you.

    Thanks,
    Sam

    ReplyDelete
    Replies
    1. Hello there, Sorry for the delayed response. In the add custom view syntax, viewIsDefault - is the setting to true to make this view as default and we are doing it in js. So I would suggest you to try something. if you navigate to your form and the double click on the lookup to view the properties of the lookup field ( on which you are trying the filtering). Just above the bottom line you have the view selector option. Turn that to off and then apply the custom lookup filtering js.

      Delete
  13. Hi Manjaly.....
    Great blog....!! There is only one problem which I am facing implementing in this way. I am not able to sort the items using filtered view....It seems code - "" is not working for me. Else, everything is as expected. Could you please help me in this?

    ReplyDelete
  14. Hey, sorry the line of code I was talking about is "order attribute='fullname' descending='false'". It seems this does not work in the filtered lookup.

    ReplyDelete
  15. Thank you Manjaly! this is really useful for a beginner.

    ReplyDelete
  16. was useful .. thank you.

    ReplyDelete
  17. When I do this, the lookup grid always selects the first result by default. This is misleading. Do you know what might be causing this?

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

    ReplyDelete
  19. It works well on IE and Firefox but not working on Google Chrome ( I am talking about the PC version not the mobile one). Any help ?

    ReplyDelete
  20. Hi ,

    I am facing same problem too. It works well on IE and Firefox but not working on Google Chrome at all.
    any help will be appreciated.
    Thnaks

    ReplyDelete
  21. Filtering criteria is not working in chrome. It works fine on IE. Has anyone had a similar problem?

    ReplyDelete
  22. Hello. I have done as suggested. But after i save the form the value of the new lookup field disappears. I want it to be retained .I m using CRM 2011. Kindly help

    ReplyDelete
  23. how can i put condition text to Search field in Lookup form, thanks alots :)

    ReplyDelete
  24. Go India Tour and Cabs leads in providing Taxi service in Jaipur that offers the most luxurious and reasonably priced taxi trips in world-class vehicles. We offer cab in Jaipur travel, outstation trips, weddings, corporate events, airport services, and sightseeing in Jaipur.

    ReplyDelete