Filter Lookup Field by Related Subgrid Records in CRM

Views (5040)

Aileen Gusni

44,524

Like(2)

Report

Sometimes we have requirement to choose lookup field value from the records that are listed in the related subgrid and we need to validate it so that the users won’t choose wrongly.

Let’s start with the example first.

You have lookup field in the Lead entity and you have a subgrid to list all of related Contractors.

Now, you have requirement to let the users to choose Primary Contractors from that list and validate it same as CRM did for selecting Primary Contact in the Account entity.

And if Look Up More Records

Yes, you can build a custom view or custom filter in the Lookup field, but is that too overkill? And let’s say you don’t have enough time or scripting skills so, what should you do?

In this post I am explaining about how to Filter a lookup field to show only records listed in the Related subgrid records.

I will use customized Case as another scenario with same concept with my first example and also same concept with Primary Contact in the Account entity.

Scenario: When you will need this circumstance?

In a Case entity, I have customized to be a Job Sheet Case for Spare Part Service Industry purposes and I have a subgrid to list all of the Case’s related Product Parts that want to be repaired as reparation service..
Now user A will have task to list all of the Parts.

Case and Product Parts are having N:N Relationship.

And I have a lookup to choose what is the Main Part that having major issue.

My client wants another user or the user himself to choose the Main Part with most major issue to choose and the system has to validate only to show the part from the available list, not another part, this is for analysis and escalation to next level purposes.

So..What can I do?

I know I can have a customer filtered lookup using javascript, but in fact CRM has capability to achieve this requirement without any custom code.

Resolution

1. Go to the Form Editor

2. Double click the lookup field to go to Field Property

3. Edit this property

Tick the ‘Only show records where:’

And to be safe, don’t allow users to turn off filter

4. You can also modify this additional property to manage the View

It will show when you click the Lookup in inline lookup and as well in the lookup more records.

To know more about the inline lookup, you can refer to this post:

5. As usual, Ok, Save, and Publish it.

Result:

Inline Lookup

*Before:

*After:

Lookup More Records..

*Before

*After

http://missdynamicscrm.blogspot.com/2014/09/crm-2013-modify-columns-shown-in-inline-lookup.html

Term and Condition

This is only works if the current entity and the entity records you want to filter are having relationship, either 1:N or N:N relationship.

Additional Info

So, now you know that without any custom code you can filter lookup to show only records from related entity without any custom code.

In my previous post, I have explained about how to filter lookup field by linked entity (only for N:1 relationship or Parent entity, for example relationship between Case and Account) fields as criteria.

In this post, we know how to filter lookup field by linked entity that having 1:N from this current entity record, no need any custom code. Even, filter lookup by related child records is easier.

So, those are tips and tricks to filter a lookup, choose your way to experience happy CRM-ing.

I hope this post can help you!
Thank you.