Filtering Custom many to many tables with intermediary table

(0) Share

Report

Posted on by MH-02091453-0

125

Hi,

I am trying to filter tables with lots of a 'many to many' relationship that has a intermediary.

I have a Student (1) ---->(Many) Student_Skills (Many)----> (1)Skills_Name

I want to be able to filter students by a chosen skill . e.g. find me all the student whose skill is dancing .

In SQL I would use either join or nested select statements

e.g.

select student name

from student where id in (Select studentId from student_skills where skillsId IN (Select id from skill_name where skillName = "dancing"))

I have tried nested filters but it doesn't work 😞 Any help much appreciated...just starting out with dataverse and power apps...the idea is to filter a gallery in a canvas app

Thanks

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (9)

Answers (0)

a33ik 3,306 Most Valuable Professional on at

Like (0)

Report

Hello Michelle,
I believe you can use Dataverse's Advanced Find to build the query you need - https://docs.microsoft.com/en-us/dynamics365/customerengagement/on-premises/basics/save-advanced-find-search?view=op-9-1
Once the query gives you the data you need you can export FetchXml and use it in your Canvas App.

Was this reply helpful? Yes No
Ram Prakash Duraisamy 5,593 Super User 2025 Season 2 on at

Like (0)

Report

Hello @MichelleH,

That was Awesome Query, Could you please let me know you want to Filter a Lookup or Filter a Subgrid, so that i can help u the same.

Note:

As per your SQL Statement we can do below operations to Achieve the same

1. (Select id from skill_name where skillName = "dancing") --> Odata and Filter it and Store it in Array
2. Select studentId from student_skills where skillsId --> Pass the 1 (Filtered Array here and Filter it)
3. select student name from student where id in 2 --> Pass the 2nd Filter Value

We can achive it OData Call,

Need a Clarity weather its look up or Subgrid.

Please mark as Answer if it is helpful and provide Kudos

Subscribe : https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA
Blog : https://microsoftcrmtechie.blogspot.com

Was this reply helpful? Yes No
MH-02091453-0 125 on at

Like (0)

Report

Hi,

i am not sure if I understand the idea of advanced find and using it in the canvas app...is this dynamic? can I use the advanced find and then the user chooses the skills they want to filter by?

From what I understand I can use advanced find to find create that particular view but as I have over 300 skill and then have other tables I need to link to with courses, so for example find students who can dance and study music, if I use advanced find is there any way of using dropdowns or user input to filter the table in the canvas app? There are too many combination for me to pre-create views for each situation. If you understand I want a series of dropdowns that allow the user to select? I feel i need some filter and look-up commands associated with dropdown input that can dynamically filter the view

Was this reply helpful? Yes No
MH-02091453-0 125 on at

Like (0)

Report

Hi,

Thanks for your reply.

So there are 3 tables, one student( 1- many), one student skills and(many to one) one skill names. The idea is that I need to display all the students with a certain skills/ skills, so the user would select filters from several dropdowns and pick which skill they want to filter, so there are two lookups in the intermediary student skills table, one that references the studentgui and the other the skill name guid. Not too sure how i store the odata feed in the canvas app as an array? Do you have any references to how to do that?

Was this reply helpful? Yes No
a33ik 3,306 Most Valuable Professional on at

Like (0)

Report

What I wanted to suggest is to use Advanced Find to build the FetchXml query that you will be able to use to filter the data based on the skills selected.

Was this reply helpful? Yes No
MH-02091453-0 125 on at

Like (0)

Report

Hi,

Ah ok so if I understand correctly i will basically join all the tables (and create one big table) I want in the fetchxml and then I can create a view from that which I use in my canvas app and then filter that big joined table? Do you know any good links to help with fetchxml...sorry I am really new to all this, used to just using sql.

Thanks so much for your help.

Was this reply helpful? Yes No
a33ik 3,306 Most Valuable Professional on at

Like (0)

Report

You can try this website - http://www.sql2fetchxml.com/

Was this reply helpful? Yes No
v-xiaochen-msft on at

Like (0)

Report
Hi @MichelleH ,

I did a test for you.

ForAll(Filter(TestL19S,LookUp_TestL20.Name=ComboBox1.Selected.Name),LookUp_TestL18.Name)

Best Regards,

Wearsky

Was this reply helpful? Yes No
MH-02091453-0 125 on at

Like (0)

Report

That is great thanks. However, I wanted to stucture it in the in way as in the sql statement as then i want to be able to also filter another custom many to many relationship, so I would also have student (1-many)---> student_enrollment --->(many-1) course_name. So then I need to start from the student table if you understand so I would be able to check the studentName that is in both the skillintermediary table and the cenrollments intermediary table, so essentially:

studentname from studenttable, where studentId in (studentid from student_skills table where skillName = 'dancing') and student id in (studentname from student_enrollments where coursename = 'Music)

So i need to start from the tsudent table so then I can then reach other tables with the courseName in and also filter on those!

Thanks in advance for any help and time.

Was this reply helpful? Yes No