Skip to main content

Notifications

Community site session details

Community site session details

Session Id :
Power Apps - Microsoft Dataverse
Answered

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

Like (0) ShareShare
ReportReport
Posted on 24 Oct 2023 10:53:44 by 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.

RiccoHipp1903_1-1698144567858.png

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:
  • Verified answer
    RiccoHipp1903 Profile Picture
    284 on 02 Nov 2023 at 05:52:53
    Re: [Model Driven] Filter a LookUp on related rows with Many To Many Relationship

    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

  • ivan_apps Profile Picture
    2,187 Super User 2025 Season 1 on 25 Oct 2023 at 10:39:15
    Re: [Model Driven] Filter a LookUp on related rows with Many To Many Relationship

    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.

  • Jonathan Manrique Profile Picture
    2,678 on 25 Oct 2023 at 07:41:37
    Re: [Model Driven] Filter a LookUp on related rows with Many To Many Relationship

    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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Power Apps - Microsoft Dataverse

#1
stampcoin Profile Picture

stampcoin 15

#2
ankit_singhal Profile Picture

ankit_singhal 11 Super User 2025 Season 1

#3
mmbr1606 Profile Picture

mmbr1606 9 Super User 2025 Season 1

Overall leaderboard

Featured topics