Filtered Lookups in Editable Grids for Dynamics 365

David Mochrie, 16 May 2018

Editable grids are a very convenient way to update data in Dynamics 365.

Lookups, and specifically filtered lookups, warrant special attention in editable grids as some of the out of the box behaviour may surprise you.

Quick Recap on Filtered Lookups

Filtered lookups allow you to display a subset of records when looking up to an entity. For example, the Account entity has an out of the box lookup to Contact called ‘Primary Contact’. As standard, this lookup is filtered to only show Contacts who have the current record as their Parent Account, i.e. you can only enter someone as the Primary Contact for an Account if they work at that Account. This makes sense. I’ve taken to calling these types of filtered lookups ‘Dynamic Filtered Lookups’, as they dynamically change the possible lookup records based on the record you are looking up from.

You can also set a filtered lookup to work on a view, i.e. a static list of records, rather than find records which dynamically match the record you are on. For example, let’s say you had a lookup to Contacts on an Account called ‘Company Lawyer’ (we’re assuming here the lawyer isn’t necessarily employed by the company in question), and you had a field on Contact identifying that contact as a lawyer, and a view of Contacts called ‘Active Lawyers’. You could restrict the subset of records returned in the ‘Company Lawyer’ field to just the ones in the ‘Active Lawyers’ view. That way, you ensure that users can only enter actual lawyers into that field. I have taken to calling these types of filtered lookups ‘Static Filtered Lookups’ as they always return the same possible lookup records, regardless of the record you are looking up from.

Lastly, you can combine the dynamic and static filtered lookups into one. So, continuing the example above, if I looked up a Contact from an Account, and only wanted to return Contacts who worked for the Account I was looking up from, and were lawyers, I could do that.

This Brings Us to Editable Grids

Firstly, editable grids don’t have filtered lookups applied out of the box. The lookup will show all active records for the lookup entity. So, if you enable them on Account, you’ll be able to set the Primary Contact for an Account as someone who doesn’t work for the Account via the grid, but not via the form.

Form:

Editable Grid:

This is a very inconsistent user experience and should be avoided.

So, for each view of the Account which has the Primary Contact as a column, you should add a filtered lookup. You do this by going to the entity in Customisations, selecting the Controls tab, selecting the Editable Grid control and selecting Add Lookup.

You will be presented with this dialog:

Select a view in the Available Views field. In the Available Columns, you’ll be able to select any lookup displayed in that view. In the case of My Active Accounts, the only lookup field is Primary Contact. When you select that you can then select the view to use in the editable grid’s filtered lookup and also optionally decide if you want to return only a dynamically filtered subset of Contacts, based on the record you are on.

You also get the option of allowing the user to turn the filter on or off, the same as when you configure a filtered lookup on a form. If you tick that box, you get a toggle control at the top of the list of returned records on the editable grid, allowing you turn the filter on or off:

Filter off:

Filter on:

This is a better user experience than on the form filtered lookup, as there you have to go into another dialog to disable the filter.

Another feature of the editable grid lookup, is the dynamic way it filters the list based on what you enter in the field. In the picture below I simply typed ‘su’ and didn’t press enter:

It even works on text inside a field:

You do have to type a minimum of 2 characters before it searches.

Please note, there are no wildcard characters, i.e. ‘*’ in editable grid searches. If I typed ‘*rick’ I’d get no results. That’s not the same as searches elsewhere in Dynamics 365. But it must be said, this type of search is very user friendly. If Microsoft replicated this type of searching where it finds as you type in other areas of CRM it would be awesome!

A limitation of the editable grid lookup is that it returns only the name of the record, no other fields. This could be problematic if it’s looking up a very big list of records, e.g. contacts. Chances are, you’re going to return multiple records with the same name, and you’ll have no way of knowing which is the right one, as you’ll simply have the name, e.g. ‘John Smith’ in the list.

So, in summary, if you enable editable grid for an entity, and you have a field on some views which has a filtered lookup applied to it on the form, I’d recommend applying the same filtered lookup to the views.

Happy CRM’ing everyone!