Sunday 9 November 2014

Filter Lookup Field by Related Subgrid Records in CRM

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.

image

image

And if Look Up More Records

image

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.

image

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.

image

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:’

image

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

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

image

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:
image

*After:
image

Lookup More Records..

*Before
image

*After
image

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.

9 comments:

  1. Hi,

    can you please indicate in which environment are you doing this?

    I tried in MS Dynamics CRM 2015 on premise, and it works only partially. When I open the lookup inline, I see records un-filtered (10 records) - not ok. If I press on "Look Up More Records" I see correctly the records filtered (7 records) - these are the records I expect to see also inline...but i don't...

    Can you please advise?

    Thank you,

    Lorena

    ReplyDelete
  2. Hi again,

    I did many tests, and seems like there is no problem if I use this approach with custom entities.
    The problem in my case is on the Contact entity form. The desired functionality should be: the manager (responsible) of a contact should be a contact in the same Company. For example: let's say I have 3 contacts in CRM: Anna working for company A, Edy working for company B and Laura working for company A. Laura is responsible of Anna (in company A), so on Anna form, when I press on my custom lookup for manager, I would like to see only Laura (being the only contact besides Anna working in company A), and I should not see Edy (because he is not in company A).
    With "filter by related" functionality, when I see the inline records of possible managers of Anna, I see Laura and Edy (NOT OK), but if I open the more records lookup page, I see only Laura (OK).
    God how annoying is this CRM full of bugs!

    Lorena

    ReplyDelete
  3. Hi
    What if we are on the form of a different entity and want to implement this lookup mechanism for 2 other entities that share a N:N relationship?

    ReplyDelete
  4. Hi

    I have a requirement in which I want to filter sub grid lookup with related records in MSCRM 2015 online. On Case entity, I have a lookup of Entity A and Grid of Entity B. Entity B records are also having lookup of Entity A. Now when I will select lookup value of Entity A on Case entity, I want to filter sub grid lookup of Entity B as per that selected value of entity A.
    Please suggest as related record filtering is not working on that.

    ReplyDelete
    Replies
    1. Hi Geetika,

      I am having the same requirement. Can I please know whether you found a solution to this?

      Thanks.
      C

      Delete
  5. Seems to not work in CRM 365 atm. Very annoying that this feature broke....

    ReplyDelete
  6. Excellent article...to make movies visit Rave Films Production

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

    ReplyDelete
  7. Fantastic article to go through. Nice informative blog. Thanks for sharing. Keep updating.
    Customized CRM Solutions
    Customer Relationship Management Solutions in Bangalore

    ReplyDelete

My Name is..