Taking your lookup filtering to the next level within Microsoft Dynamics 365 CRM

Before we get into this content, I’ve got an update. I wrote a blog about 6 months ago about adding custom filtering to a lookup on a field – it involved a little bit of JavaScript but worked really nicely. Obviously, when I showed this to a few users, they loved what it could do and requested it was implemented in a few more places.

I set about applying it to the new areas but, on one form, it just didn’t work. After lots of head-scratching, it turns out that applying JavaScript filtering on top of standard form filtering causes some very unintended consequences. So, this was the first gotcha that I learned; make sure the standard lookup filtering is switched off.

The New Requirements 

One of the new requirements presented to us involved an interesting combination of records. The business requirement started with a supplier; we needed to list what countries they could supply to and, for each of these countries, what currency they wanted to deal with. These were added with a child table linked to the supplier listing the country and the currency – if the country was listed, they could supply to it and then it was a question of how many currencies were linked. The child table was fairly simple, just lookups to the supplier, the country and the currency.

I tried to use the functionality I mentioned in my previous blog post but it just didn’t work. It turns out, that adding custom filtering to Microsoft Dynamics 365 CRM is limited to the <filter> part of the fetchXML – everything else was ignored and as this new request needed related tables, it wasn’t going to cut it.

Luckily, after a bit of research, I found out that this could be taken to a whole new level by adding a dynamically created view.

This allowed not just the filtering element of the fetchXML but everything about the view. So, in order to do this, I had to go through a few extra steps. Also, let me just say, that if you want to just filter based on information in the form, the addCustomFilter is still the way to go.

However, if you need the extra functionality of the custom view within Microsoft Dynamics 365 CRM, you’ll need to gather a few pieces of information to be able to use it:

• viewId – this needs to be a GUID that’s NOT one of the existing view IDs. It’s not saved so can be anything you like – something along the lines of ‘12345678-1234-1234-1234-123456789012’ works fine.
• entityName – the schema name of the table that’s being returned. Obviously, this needs to be the same as the table that’s linked in the lookup. This will be something like ‘account’, contact’, etc and NOT the pluralised version like ‘accounts’, ‘contacts’, etc.
• viewDisplayName – a name for the view. Again, this isn’t saved so just needs something in here as its title.
• fetchXML – the full fetchXML string. I’m assuming that you’ll want to have dynamics values from the form in here so make sure they’re in the right format and contain data if they’re used in this or you’re going to get an ‘Invalid Argument’ or ‘QueryBuilder Error’. There’s some examples of this in my previous blog post.
• layoutXML – these are the columns that make up the view. As with a standard lookup view, three columns are recommended. I’ll look at this later on.
• isDefault – whether to use this view as the default; adding ‘true’ makes sense here.

So most of these points should be fairly obvious. I would assume that 95% of the effort of putting this together will be focussed on the fetchXML and layoutXML components.

To get the fetchXML, as before, the easiest way is to create an advanced find then click on the Download FetchXML button.

This will give you the fetchXML. You’ll need to bring this into your JavaScript code as a variable.

The layoutXML isn’t so easy but there is a way around this – not as simple as just clicking a button but it does work. In your advanced find, add in your three columns then display your results. This next bit will depend on your browser but you’ll need the developer tools – usually accessed by pressing F12 on your keyboard but there’ll be a method of accessing these. When the source is showing, search for ‘layoutXML’ – it should find a line with this in. When you’ve found this, you’ll need the section of code that starts with ‘<grid…’ and ends with ‘</grid>’

Once you’ve got these, you just need to bring these together with the following line:

formContext.getControl(“fieldname”).addCustomView(
  viewId,
  entityName,
  viewDisplayName,
  fetchXml,
  layoutXml,
  isDefault)

Once that’s there, the only thing to do is add it to your form. The getControl(“fieldname”) refers to the field that you want to add this to and it’s just a simple case of calling this. Wrap it all up inside a function and call it whenever the bounds of your search needs to be refreshed.

The only gotcha I seemed to find with this code is that, whilst I could add two layers of searching to my fetchXML, it only seemed to take one layer into the custom view – not a massive problem but it did flummox me for a while.

Applying this to the form

Going back to our user case, I needed to apply this on the form within Microsoft Dynamics 365 CRM for selecting a supplier and a currency. Firstly, we add a customer onto the form and we know from this record, which country the goods have to be shipped to. So, when it comes to selecting the supplier, we need to filter it down to the records who are linked to the country. I created the advanced find involving the related table and downloaded the fetchXML – the relevant part being:

<link-entity name="tm_rgncur" from="account " to="accountid" link-type="inner" alias="aa">
  <filter type="and">
    <condition attribute="tm_country" operator="eq" uiname="Italy" uitype="tm_country" value="{00000000-0000-0000-0000-000000000000}" />

This was included in the code and applied to the lookup for the supplier (for ease of reading, I’ve replaced the variables with ‘Italy’ as the country but, obviously, in the actual example, these would be dynamic.

Once the supplier was selected, I ran another refresh on the currency lookup to filter this down, the relevant part of the fetchXML being:

<link-entity name="tm_rgncur" from="tm_currency" to="transactioncurrencyid" link-type="inner" alias="ab">
  <filter type="and">
    <condition attribute="tm_country" operator="eq" uiname="Italy" uitype="tm_country" value="{00000000-0000-0000-0000-000000000000}" />
    <condition attribute="account" operator="eq" uiname="My Supplier" uitype="account" value="{00000000-0000-0000-0000-000000000000}" />

Again, this fetchXML was updated dynamically with values from the record but it filtered this exactly as we needed it.

That solved the problem, a multi-levelled dynamic filtering of reference data to provide an accurate list of what’s allowed.