Filter data in a CRM lookup field
Curt Spanburgh posted this message on the Sandbox: Custom Lookup Dialog for Microsoft Dynamics CRM 3.
In this post he describes that it is possible to filter the data in a lookup box. He also gives an example which uses data on the form to filter the lookup data. For testing purposes I have used the code below. It should be placed in the form onload of the account entity. It does filter the lookup parent account and will only show accounts which do have a parent account already. Not necessarily useful in a business case, but it does give you a clear example.
crmForm.all.parentaccountid.lookupbrowse = 1;
crmForm.all.parentaccountid.additionalparams = "fetchXml=<fetch mapping='logical'><entity name='account'><all-attributes/><order attribute='name' descending='false'/><filter type='and'><condition attribute='parentaccountid' operator='not-null'/></filter></entity></fetch>";
crmForm.all.parentaccountid.additionalparams += "&selObjects=1&findValue=0";
As you can see, some extra values are sent to the additionalparams attribute. These are selObjects and findValue. The selObjects should be set to the enity id of the lookup its entity.
To help you create fetchXml queries look here: Using Advanced Find for FetchXml
Great tip Curt!
Update: This does not work in CRM 4.0. Instead, look at the add-on as developed by Stunnware: http://www.stunnware.com/Products/FLD4/Default.htm
61 comments:
The filter works. However, I am unable to select OK once the lookup is filtered.
Any ideas?
I used the code you provided and it works great when I am previewing my form. But, when I try it in our system it doesn't work. I read somewhere that form-assistant won't use the supplied query. Any suggestions??
Hi,
Make sure that all the quotes are closed as well, that might make a difference. The OK button is working here. The code as supplied in my post works on the CRM May VPC. Does it work in your place using the code?
For the second comment I can indeed say that it wont work for the form assistent. I haven't found a solution to do that. Does the filter work on the lookup itself?
Ronald
Hello,
I think your codes and examples are great. I have learned a great deal from your Blog. It has helped me achieve the goals I need.
Your lookup filter is working great for me. It solved a big issue I had with a new entity I had created. Also the Form assistance is good, I like the fact I have an option to select others. In our business we want to know who opened the call, which can be a non-employee of the account.
I do need your assistance on a couple of items regarding the code you have provided above:
1. I am using it in a few areas; one of them is the Responsible contact for a case. It filters the lookup to show only employees that work for the account. If the contact is not in the list I would want to add a new contact. The “New” button is there, but when I click on it, I get an IE error of a failed script with the code similar to the Fetch statement. Any Ideas on how I can fix this?
2. I read in other feeds that you had commented on. A user was trying to filter the Primary Contact for an account within the account entity. You recommended that the fetch statement will work to solve the issue at hand. Based on your code and others that have tuned it, it is searching for the Accountid. The account Entity does not show or am I able to identify the accountid via any script. Every time I attempt to get eh Accountid from the account entity I get this error:
“crmForm.all.accountid.DataValue is null or not an object.”
This tells me that the accountid is not on the form. This happens to any account that has an id already. What I am trying to do is to find out if the accountid can be accessed from the account entity.
Any thoughts on it?
Hi John
I have updated the code sample in my post. It includes now the selObjects and findValue attributes. You should update your code to use these as well and keep in mind that you set the selObjects value to the correct entity id. That would solve your first issue. The second issue your experiencing is because the accountid is not an attribute field on the account page. You can access the current account id from the account form by using the code "crmForm.ObjectId" instead of "crmForm.all.accountid.DataValue".
Hope this helps!
thank you I will try that out.
Just a few minutes ago, I found out how to get the Accountid via the Objectid.
I will change my code to what you suggested. I will reply.
John
Hello Ronald,
I had to play with your script and this is what worked for me:
crmForm.all.primarycontactid.lookupbrowse = 1;
crmForm.all.primarycontactid.additionalparams = "fetchXml= fetch mapping='logical'> entity name='contact'> all-attributes/>filter type='and'> condition attribute='parentcustomerid' operator='eq' value='" + crmForm.ObjectId + "'/>/filter>/entity>/fetch>";"
crmForm.all.primarycontactid.additionalparams += "&selObjects=1&findValue=0";
I can create a new contact if contact is not in the list.
Thank you very much for your help.
John
Hi John,
Thanks for posting your solution. It allowed me to notice one mistake. The SelObjects is set to 1 altough it should be set to 2! The entity you're going to create is a contact which has the entity typecode of 2. Furthermore you should check if the form is a create or update. If it is a create, then you should show all, or none, contacts in the lookup. Now it will generate an error because the entityId of the form is nonexistant.
Last thing to notice is that this commenting on the blog doesn't allow you to write the 'less then sign' in comment posts. Here is the code which should work when copy pasting. It does have the SelObjects = 2 fixed, although no code for the Update/Creation checking.
crmForm.all.primarycontactid.lookupbrowse = 1;
crmForm.all.primarycontactid.additionalparams = "fetchXml=<fetch mapping='logical'> <entity name='contact'> <all-attributes/><filter type='and'> <condition attribute='parentcustomerid' operator='eq' value='" + crmForm.ObjectId + "'/></filter></entity></fetch>";
crmForm.all.primarycontactid.additionalparams += "&selObjects=2&findValue=0";
Thanks for posting this solution. I'm implementing this filter on a custom entity lookup based on another custom entity lookup.
It works without error if i do not put anything in for "value" in the fetch XML, but if i try to use 'crmForm.all.new_parcelnumberid' or ' " + crmForm.ObjectId + " '
i get an error message requesting that value equal a 32 character GUID
Any ideas?
Gentelmen,
What is wrong in this piece of code - it does not filter anything:
strMiejscowosc = "Legnica";
crmForm.all.new_rel_salawykladowaid.lookupbrowse = 1;
crmForm.all.new_rel_salawykladowaid.additionalparams = "{fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>{entity name='new_ent_salawykladowa'>{attribute name='new_ent_salawykladowaid'/>{attribute name='new_name'/>{attribute name='new_atr_miejscowosc'/>{order attribute='new_name' descending='false'/>{filter type='and'>{condition attribute='statecode' operator='eq' value='0'/>{condition attribute='new_atr_miejscowosc' operator='like' value='" + strMiejscowosc + "'/>{/filter>{/link-entity>{/entity>{/fetch>";
crmForm.all.new_rel_salawykladowaid.additionalparams += "&selObjects=10002&findValue=0";
(I had to change < to { to make blog accept the post)
The FetchXml statement is copied from Advanced Search (as in other postin this blog).
I will be grateful for any help,
Piotr Sagnowski
Hi Piotr,
The code seems fine to me. I can't test it because I dont have the customizations as you have them. Try to find out in the SQL Profiler which SQL statement is being sent to the database. This usually gives me more insight if the statement is correct.
Let me know how you proceed, I might have some time later this week to dig into your issue.
Kind regards,
Ronald
Thanks for the fix. I did not relize it will create an Account and not the contact. one of my users noticed it and informed me. I have too much to do and not relized it was creating teh wrong entity.
thank you.
Hi,
THe code is not working for me when I place it in onload of account entity?
How do we show only the contacts for a particular parent account?
Can we develop generalized custom lookup in common to all entities.
please do the needful.It is required very urgently.
Many thanks in advance
Hi,
The code is not working with me.
How to retrieve only contacts to a partcular parent account?
Can we bulid a generalized custom lookup for all entities?
please help me as it is required urgently.
Many thanks in advance
Hi Ronald,
where do we find information about lookupbrowse,additionalparamas...these are not listed in CRM SDK?
many thanks in advance
Hi shilpa,
There is no documentation around these values. And because it is not in the SDK, it will not be supported by Microsoft. If you decide to use these codes, then it is your own choice.
The code as given in one of my comments earlier should cause all the data in the lookup to be contacts who have the parent customer set to that account.
If you need to do more with lookups, then contact stunnware (michael höhne). He has a custom lookup which allows you to do more.
The code for primarycontactid works perfect. Thanks for sharing.
I am also looking for a similar code, but than for appointments / partylist. I managed to changed the field "optionalattendees", it display's now only the company (entitycode 1 & only one kan be selected). The field "requiredattendees" can only contain contact (entitycode 2).
It would be nice if the lookup filter could manage also this lookup field, but since it is a partylist, I did not get it to work.
Some idea's here?
+ added a condition so only records with statecode = 0 (active) are returned
This post was VERY helpful. I was looking for a way to limit the "Primary Contact" lookup on the Account Form to only those contacts who are associated with the account. This is what I came up with (less-than character replaced with ^):
var CRM_FORM_TYPE_CREATE = 1;
var CRM_FORM_TYPE_UPDATE = 2;
switch (crmForm.FormType)
{
case CRM_FORM_TYPE_CREATE:
// do nothing
break;
;
case CRM_FORM_TYPE_UPDATE:
var accountID = crmForm.ObjectId;
//alert (accountID);
crmForm.all.primarycontactid.lookupbrowse = 1;
crmForm.all.primarycontactid.additionalparams = "fetchXml=^fetch mapping='logical'> ^entity name='contact'> ^all-attributes/>^filter type='and'>^condition attribute='parentcustomerid' operator='eq' value='" + accountID + "'/>^condition attribute='statecode' operator='eq' value='0'/>^/filter>^/entity>^/fetch>";
crmForm.all.primarycontactid.additionalparams += "&selObjects=2&findValue=0";
break;
}
Create Account forms are left the way they were, but Update Account forms will filter the lookup results.
I'm putting it here in case anyone else was looking for the same solution. Keep up the good work!
Hi all,
This i a great solution, but i still have a problem. I can't create new Contact while using the filter. Can somebody help me? I'm using the updated code:
crmForm.all.primarycontactid.lookupbrowse = 1;
crmForm.all.primarycontactid.additionalparams = "fetchXml=fetch mapping='logical'> entity name='contact'> all-attributes/>filter type='and'> condition attribute='parentcustomerid' operator='eq' value='" + crmForm.ObjectId + "'/>/filter>/entity>/fetch>";
crmForm.all.primarycontactid.additionalparams += "&selObjects=2&findValue=0";
Any ideas?
(in my code i'm using the tags, here i'm not allowed to post them)
Hi Adi,
I copied and tried your code and your code DOES work :)
The new contact does not show up because you have a filter set. If you would go to the contact list and search for your contact, you will find it. Then also set the parent account of that contact and you will be able to find it back in the filtered lookup data.
One way to catch this is to add the parent account to the list of required or recommended fields. By doing so it will show up on the quick create page and you will be reminded to set the parent account. it will show up at once then in the list.
Hope this helps,
Ronald
The problem is this: When i click the New button in order to quick create a contact that is not in that filtered view, the quick create windows pops up, but i get this message: "An error has occured. For more informations contac your system administrator". I really can't figure out what's the problem only from that message. I have no idea.
Hi Adi,
I'm not getting that error message. Could you send me your customizations file? ronaldl at avanade dot com.
Also, have you installed rollup 1 or not?
I did'nt install it yet. I'm gonna do it right now. I'll let u know if i get the error again. Thanks for the tip. I didn't install it yet because i'm only using that machine for testing purpose.
Simply GREAT !
Hi Ronald,
The solution is great!
But as always new solutions provide new questions :-)
The original lookup contains a search field, the new one doesn't...
Question: is there any way to keep the search field but combine it with the filter?
If not: thanx anyway for the solution!
Hi Bertil,
I can't find a way to enable the find box after using the filter. Therefore make sure that the filter does return a small set of data.
Kind regards,
Ronald
Hi,
Thnx for the guide. I used this code in opportunity form where potential contact will be filtered from selected account name. It shows error:"Entity name specified in FetchXml does not match the entity name in the Entity Expression"
{
crmForm.all.new_potentialcontactid.lookupbrowse = 1; // This turn on filter
crmForm.all.new_potentialcontactid.additionalparams = "fetchXml=^fetch mapping='logical'> ^entity name='opportunity'>^all-attributes/>^filter type='and'> ^condition attribute='new_accountnameid' operator='eq' value=' " + crmForm.ObjectId + "'/>^/filter>^/entity>^/fetch> ";
crmForm.all.new_potentialcontactid.additionalparams += "&selObjects=2&findValue=0";
}
pls drop suggestion for to me fix this...
TQ.
Hi TQ,
In your fetchXML you are specifying the entity to be opportunity. That causes that the selObjects should be set to the id value of opportunity. Currently it is set to 2, which is contact. You can find the correct id when opening up the details for the opportunity entity from this page:
http://yourserver/sdk/list.aspx
Good luck,
Ronald
Hi,
Your post really helps me a lot. Thank You Ronald. I able to filter contacts from a selected account.
May i ask, if only one record is available at the filtered record and if i want to make it selected automatically onLoad event; is it possible? if yes, how to do this?
Sugu.
Hi Sugu,
What you want is to execute quite some business logic. What you can do to achieve this, is to build all this code into javascript including webservice connections to crm.
Another approach would be to build your own webservice which does return some values. If you connect to this webservice using Javascript in the form onload, then you can set the value in the lookup.
The second approach would be how I will solve this question.
It is some work, but definitely good possible.
Good luck,
Ronald
Hi Ronald,
Could you tell me what are the params: "selObjects" and "findValue" used for?
I didn't include them and my filtering worked fine.
Just curious to see if they can enhance anything?
Thanks
Hi, this code really works. But there's a problem. This filter does not work for more action when editing multiple records. Is there any example for that?
Hi,
A poster mentioned that they did not see the 'OK' button. The reason for this I believe is that they did not specify the ID of the entity they're performing the lookup on. As I wanted to minimize the amount of data retrieved from the server, I omitted the 'All Attributes' value and instead specified the name and createddate attribute. When this was provided it did not show the OK button until I provided the Unique Identifying field.
Cheers,
Karlo
Hello everyone... I would like to know if somone could find the solution of the OK Button... I have the same problem and I don't know what to do...
How can I post the code??? I get an error: Invalid tags" I have created a relation with another entity that I want to show you...
Thanks!
Pablo
Hi Pablo,
As you can see in the posting made before yours, the solution is to include the id attribute in the fetch query. One of my colleagues had the same problem and was solved by adding <attribute name="new_entityid">
This is an awesome piece of code that should have been incorporated and supported into since the release of CRM 1.0, in my opinion. However, this apparently does not work at all in Titan, so we will back to square 1 again.
Hi Ronald
Your Posts are always helpful.
Thanks for them.
I am looking for one enhancement in lookup filter functionality, actually when we click on filtered lookup we dont get "Look For" and "Find" options in Lookup Browse window.
Is there any workaround to get it.
Hi Ronald,
Thanks for your post.
It really does wonders....
Just hope that Titan has some of this stuff in its new version.
Hey could you just help me out in my problem.it is some what related to lookup.
I have a custom form in CRM.
It has two lookup fields, one contact and one for account.
I need to populate the account field with the parentaccountid whenever a contact is selected from the contact lookup.
Although I am able to read the GUID of the parent account of the selected contact through javascript but I am not able to populate the account lookup field with the same value.
Please help me out.
Thanks in advance.
Susan
Ronald -- what do you suggest that customers do for any unsupported customizations during an upgrade to Titan?
We had a number of non supported customizations during our 1.2 upgrade and learned a valuable lesson! we ended up paying for a CRM - CRM Datamigration due to the fact that we had tweaked CRM beyond the scope of an upgrade. The upgrade to Titan is the same -- any unsupported customizations may not allow an upgrade to succeed.
Hi All,
I would like to filter the parentaccount lookup of the account entity against a picklist attribute.
Say the picklist is 'accountlocation' and it contains values 'brazil','spain','italy'.
Now only accounts having accountlocation = 'brazil' will be shown in the parentaccount lookup.
Any ideas??
Hi Ronald, your site is fantastic. I was wondering if you have been able to get this technique to work in Titan. I tried it and it breaks.
Thanks heaps.
Rob
Hi Ronald,
The filtered lookup code did wonders for me in CRM 3.0.
Recently I upgraded my system to CRM 4.0 & the code works no longer now.
I am really frustrated.
I have a workaround to use the Stunnware filtered lookup but I really do not want to use that....
Would really anticipate to know if you could suggest some javascript to get the filtered lookup working in Microsoft CRm 4.0
Thank n advance,
Susan
Hi Susan,
For now the only solution is to use the Stunnware solution. I think the chances are small to get it working with just a javascript...
Kind regards,
Ronald
Hi Ronald. I love your site. Learned a lot from here. However, my boss expects more :)
We are working in CRM 4.0 now. There are two lookup fields in Case Form - Customer (based on Accounts) and Preferred Engineer (based on Users) I need "to tie together".
There is Preferred Engineer field in Account Form as well. We are choosing Preferred Engineer in Account form manually, before creating a new case. When we create a case (before saving it), we are picking up an account name from Customer lookup field. We want Preferred Engineer field in Case form to choose the same name as we have in Preferred Engineer/ Account form. The name has to appear on Change event. How to make this dream come true?
Any help is appreciated. Tatiana
u can do this by a WebService.
Great, well done and thanks for sharing ...
I am really enjoying this site, thanks. I haven't written any extensive code since my PL/1 days, so that should let you know where I'm coming from. That being said, I'm really enjoying customizing MSCRM 3.0. I have done some relatively extensive changes to the quote module, and I've got one piece that I've almost got a handle on and this is so close, but I can't quite make it work. I want to be able to select the 'account' and then filter the 'opportunity' lookup which is also on this page to include 'only' the opportunities that coorespond to that account. Thanks again for the blog. You are doing a great job of teaching this 'old dog' some neat new tricks
Every thing worked except New Button.
This is the Request URL:
http://crmserver/PITS/_controls/lookup/lookupsingle.aspx?class=BasicCustomer&objecttypes=1,2&browse=1&search= 'fetch mapping='logical'' 'entity name='account'' 'attribute name='accountnumber' ' 'attribute name='name' ' 'filter' 'condition attribute='new_managementpersonid' operator='not-null' ' 'condition attribute='new_userid' operator='not-null' ' '/filter' '/entity> 'fetch' &selObjects=1&findValue=0&ShowNewButton=1&ShowPropButton=1&DefaultType=0
the error was Invalid parameter 'selObjects=1' in Request.QueryString
please help me, since this lookup field fetch from contacts and accounts i changed it to fetch only accounts.
abipeter -
What are you using for your FilterExpression within the Lookup? Those look like 4.0 querystring parameters, are you able to get this working with 4.0?
Thanks
It's great. :)
But I've a problem,how can I set filter to the Subject lookup? (I need to show only one node of the tree,for example.)
Hi CRMuser,
I have not tried that before. Unfortunately I'm not having enough spare time to dig into that. If you do find that out, I'm interested in the solution :)
Ronald
Hi ronald, we are looking for everywhere on internet to find a solution for filtered subject tree. This is a real problem for most cases of us. we need to select 2 lookups subject and subsubject. thats why we need to filter subsubject field according to the subject field. I know its not really the aim of this topic but i like your approach for the filtered view. Thats why i want to ask for if there is any idea to filter a subject tree lookup
To be honest, I think the subject tree is not very useful. I would look into creating a custom entity and using that instead of the out of the box subject.
Hi Ronald
When we click on filtered lookup we dont get "Look For" and "Find" options in Lookup Browse window.
is there any way to accomplish it.
I want this for CRM 3.0
Thanks a lot Ronald for the not-null condition, basically i am sharepoint guy and new to CRM so the filter helped me a lot.
Hello Ronald,
I am trying to add a 'OR' grouping between the two conditions below.
The conditions are related to attributes from 2 entities 'product' and 'group'
where new_group is a custom entity that has a 1:N relationship with product.
-- --
I have tried , but it doesn't work.
Any ideas?
Thanks.
Hello Ronald,
I am trying to add a 'OR' grouping between the two conditions below.
The conditions are related to attributes from 2 entities 'product' and 'group'
where new_group is a custom entity that has a 1:N relationship with product.
fetch mapping="logical"
entity name="product"
attribute name="name" /
attribute name="new_groupid" /
order attribute="name" /
filter
condition attribute="name" operator="eq" param="name_param" /
/filter
link-entity name="new_group" from="new_groupid" to="new_groupid"
attribute name="new_groupid" /
attribute name="new_code" /
filter
condition attribute="new_code" operator="eq" param="code_param" /
/filter
/link-entity
/entity
/fetch
I have tried filter type='or' , but it doesn't work.
Any ideas?
Thanks.
Hi Adi,you report this problem
"The problem is this: When i click the New button in order to quick create a contact that is not in that filtered view, the quick create windows pops up, but i get this message: "An error has occured. For more informations contac your system administrator". I really can't figure out what's the problem only from that message. I have no idea."
Your problem has to do with internet explorer, try changing your security settings.
I forgot to mention that you need to change the security setting to disable XSS Filter.
And your problem solved
Post a Comment