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:
It just did not seem to work “as is” in the UCI, so there is an updated version here:
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:
Can this script be used to filter 1:N Add Existing Lookup?
Hi Michael, I think it should work for 1:N, too.
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:
Can you tell my what am I missing in order to have XrmCore?
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.
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 🙂
How you got XrmCore working in typescript ?
Yes thankyou, well explained.
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?
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
Hi Alex, How can i filter records using link entity.
You can fix this in a supported way by using a PCF control. https://pcf.gallery/nndropdown/
The control is configurable, so no scripting involved.
Does it turn the filter on for Add Existing button on the Subgrid as well or only limits to the custom control ?