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:

Anonymous said...

The filter works. However, I am unable to select OK once the lookup is filtered.

Any ideas?

Anonymous said...

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??

Ronald Lemmen said...

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

John said...

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?

Ronald Lemmen said...

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!

John said...

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

John said...

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

Ronald Lemmen said...

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";

Anonymous said...

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?

Piotr Sagnowski said...

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

Ronald Lemmen said...

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

godmcse said...

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.

shilpa said...

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

shilpa said...

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

shilpa said...

Hi Ronald,


where do we find information about lookupbrowse,additionalparamas...these are not listed in CRM SDK?

many thanks in advance

Ronald Lemmen said...

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.

Wolfgang Jansen said...

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?

Wolfgang Jansen said...

+ added a condition so only records with statecode = 0 (active) are returned

Jeff said...

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!

Adi said...

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)

Ronald Lemmen said...

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

Adi said...

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.

Ronald Lemmen said...

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?

Adi said...

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.

Ventrazza said...

Simply GREAT !

Bertil said...

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!

Ronald Lemmen said...

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

Anonymous said...

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.

Ronald Lemmen said...

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

Anonymous said...

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.

Ronald Lemmen said...

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

Alvin said...

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

Angie said...

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?

Prashanth N said...
This comment has been removed by the author.
Karlo said...

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

Pablo Gomez said...

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

Ronald Lemmen said...

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">

Nick Doelman said...

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.

Manish said...

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.

CaprisS said...

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

Anonymous said...

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.

swati said...

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??

Rob said...

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

CaprisS said...

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

Ronald Lemmen said...

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

Tatiana said...

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

Anonymous said...

u can do this by a WebService.

Anonymous said...

Great, well done and thanks for sharing ...

rblasch470 said...

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

abipeter said...

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.

Jeremy said...

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

crmuser said...

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.)

Ronald Lemmen said...

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

Anonymous said...

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

Ronald Lemmen said...

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.

CRM User said...

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

Raghavendra Shanbhag said...

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.

Anonymous said...

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.

Anonymous said...

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.

Hélder Oliveira said...

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.

Hélder Oliveira said...

I forgot to mention that you need to change the security setting to disable XSS Filter.

And your problem solved