Filtered N:N lookup

By | August 14, 2019

 

If you ever tried using out of the box N:N relationships, you may have noticed that, out of the box, we cannot filter the lookups when adding existing items to the relationship subgrids.

In other words, imagine you have 3 entities:

  • Main entity
  • Complaint entity
  • Finding entity

 

Main entity is the parent entity for the other two. However, every complaint may also be linked to multiple findings and vice versa… Although, that linkage should only be done within the main entity – if there are two main records, it should only be possible to link complaints and findings related to the same main record.

Which is not how it works out of the box. I have two main records below, the first one has 2 complaints and two findings, and the second one has one complaint and one finding:

image

image

image

There is an N:N between Findings and Complaints, so what if I wanted to link Complaint #1 on the first main record to both of the findings for the first main record?

That’s easy – open the complaint, open related findings, click “add existing” and…

image

Wait a second, why are there 3 findings?

Let’s try it the other way around – let’s open Finding #1 (first), and try adding complaints:

image

Only two records this time and both are related to the correct main record?

The trick is that there is a custom script to filter complaints. In essence, that script has been around for a while:

https://www.magnetismsolutions.com/blog/paulnieuwelaar/2018/05/17/filter-n-n-add-existing-lookup-dynamics-365-v9-supported-code

It just did not seem to work “as is” in the UCI, so there is an updated version here:

https://github.com/ashlega/ItAintBoring.FilteredNtoN/blob/master/FilteredNtoN.js

All the registration steps are, mostly, the same. There are a couple of adjustments, though:

You can use the same script for all N:N relationships, but, every time you introduce a new relationship, you need to update the function below to define the filters:

image

For every N:N relationship you want to start filtering, you will need to add one or two conditions there since you may be adding, in my example above, findings to complaints or complaints to findings. Hence, it’s the same relationship, but it can be one or the other primary entity, and, depending on which primary entity it is, there will be different filters.

When configuring command in the ribbon workbench (have a look at that original post above), there is one additional parameter to fill in – that’s the list of relationships for which you want entity lookup to be filtered:

image

In the example above, it’s just one relationship. But it could be a comma-separated list of relationships if I wanted complaint entity to be filtered for different N:N-s.

That’s about it… There is, also, a demo solution with those 3 entities(+the script) which you can import to try it all out:

https://github.com/ashlega/ItAintBoring.FilteredNtoN/blob/master/DemoFilteredSelector.zip

12 thoughts on “Filtered N:N lookup

  1. Michael Golovastikov

    Hi, Alex!

    Can this script be used to filter 1:N Add Existing Lookup?

    Reply
  2. Liisa

    Hi, Alex!

    Thank you for the post and code! I got it to work but I have one small problem. It seems I don’t have XrmCore and so I can’t use this line:
    XrmCore.Commands.AddFromSubGrid.addExistingFromSubGridAssociated
    Can you tell my what am I missing in order to have XrmCore?

    Reply
    1. Alex Shlega Post author

      Hi Liisa, turned out XrmCore is, actually, unsupported. I’ve used unsupported code – that’s worth a separate blog post (coming soon)! XrmCore seems to be working in my environment, but I’ve updated the solution just now to verify if XrmCore is available or not. If XrmCore is not there, the code will be using Xrm.Utility.lookupObjects as well. Just get the latest version from the github repo.

      Reply
      1. Liisa

        Hi, I got it to work now somehow. As I am using typescript, I created interface for XrmCore and at the moment it works in my environment. But thanks and waiting forward for your next post 🙂

        Reply
  3. Dax Kent

    This works great with one filter, but when I try to add a second filter to the XML, only the first one is used. Is it possible to add two filter conditions?

    Reply
    1. Almas

      You can add as many conditions as you like, just add them into filterXml. To make sure your filtering is working correctly, you can use XrmToolBox

      Reply
    1. Saurin

      Does it turn the filter on for Add Existing button on the Subgrid as well or only limits to the custom control ?

      Reply

Leave a Reply to themuls Cancel reply

Your email address will not be published. Required fields are marked *