[Model Driven] Filter a LookUp on related rows with Many To Many Relationship

(0) Share

Report

Posted on by RiccoHipp1903

284

Hello guys,

I have three tables: JobTitle, Department and Employee

Between JobTitle and Department is a native Many to Many Relationship (Because Jobtitle cant be part of many departments and each depatment has many jobtitles)

The Problem now. The third Table Employee has a LookUp Column to Departments and One to Jobtitle.

I want to filter the jobtitles in the lookup based on the department Lookup. *E.G. When Department is IT I only want to show related job titles and not something like "Marketing Manager".
The problem is with a 1: relationship i can activate the *Filter by related rows and can select Departments. With a many to many relationship, that won't work.

I saw some pretty hard JavaScript to filter something but nothing fits my needs.

Did someone encountered the same Problems nd what to do now?

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Jonathan Manrique 2,687 on at

Like (0)

Report

Hello

If what you have is an N:N relationship native to Dataverse, at the configuration level there is no way to show this relationship to users in the system, since in principle it is not a table visible in the system. To see it, you'd have to use XRMToolBox tools.

As a suggestion I would say, you can mix a little bit of Custom Page, and build the form and create filters there like dropdow and then show a gallery of elements. And then you embed that Custom Page within your model-driven application.

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
You can accept more than one post as a solution

Was this reply helpful? Yes No
ivan_apps 2,187 Moderator on at

Like (1)

Report

You are definitely going to have to do something custom as this isn't easily configurable. However you can probably achieve this by using a combination of fetchXML and pushing pre-filter criteria to the lookup to simplify the javascript. The javascript AddPreSearch function is what you need to add, and then create a custom filter criteria that joins against your N:N table and limits it to those that are the selected department.
https://learn.microsoft.com/en-us/power-apps/developer/model-driven-apps/clientapi/reference/controls/addpresearch

Another way you can try is to create pre-set custom views using XrmToolBox fetchXmlBuilder and create a view for 'Job Titles in the Marketing Department', 'Job Titles in the Sales Department' etc. Then you simply change the lookup view in your form via the setDefaultView function for your lookup control: https://learn.microsoft.com/en-us/power-apps/developer/model-driven-apps/clientapi/reference/controls/setdefaultview

Note that view ids would be hard-coded and you would need to ensure any movement between environments doesn't re-assign view ids.

Was this reply helpful? Yes No
Verified answer

RiccoHipp1903 284 on at

Like (1)

Report

Hello @ivan_apps ,
thank you for your help. For your interest. The AddPreSearch FUnction does not work with a Linked Entity.
But you helped me finding another way. I mixed your view Idea with the setCustomView. For all who are intrested in Filter a many to many relationship on realted rows I wrote an article for that on my website.

Model Driven App – Filter Many to Many LookUp – WorkWithPower

I hope its oke if I not accept your answer as solution because it only gave me a hint/pushed me in the right direction for searching not a really solution

Was this reply helpful? Yes No