Filtered N:N lookup

 

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:

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…

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:

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:

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:

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