Filter a Gallery with a Dropdown based on related tables

[Anonymous]

I have a table structure that is using relationships. Let's say I have 3 levels of Parent-Child (1 to Many) tables such that Project Manager is a LKUP table at the highest level, Projects is the next level (so each project has a Project Manager) and Project Milestones is the 3rd level with 1 to Many from Project -> Project Milestones.  My tables are in CDS

I have a gallery where I am trying to filter Project Milestones by Project Manager.  

My Items property of my gallery is as follows:

Filter([@'Project Milestones'], 'Projects'.'Project Manager'.Name = Dropdown.Selected.Name)

"Name" is the column name in the Project Managers table. 'Project Manager' is the column name in my Projects table  I have tried using the Items property of the Dropdown equal to both 'Project Managers' and 'Project Managers'.Name and doesn't seem to make any difference -- my dropdown is working correctly in terms of showing all of the project managers.

My gallery is showing blank no matter what I do and there are no syntax errors in my Filter formula.  Please help

[Drrickryp]

Hi @Anonymous 

I will make a few changes to the way you are designing your tables by adding a foreign key (named after the Primary ID in the Parent table) to each of your tables such that each Project now has a managerID field and each Milestone has a projectID field. To simplify your table names I would call them simply Managers, Projects, and Milestones.

You wish to filter a gallery of Milestones by manager shown in a dropdown. 

The Items property of the Dropdown will be Projects but need to show managers names use the AddColumns() as follows: AddColumns(Projects,"Mgr", Lookup(Managers, ID=managerID, ManagerName).  I would then select Mgr to show in the window of the dropdown.

In your gallery, I would set the Items property to Filter(Milestones,projectID=Dropdown.Selected.ID)

[Anonymous]

Thank you @Drrickryp  .. I am fairly new to PowerApps and CDS.  I'm not sure how I would create a foreign key in CDS.  Is that just by picking 1 column in the Keys tab?  I would think by the fact that I have a relationship between the tables already in CDS that the Lookup function you are referring to would not be necessary?

[Drrickryp]

@Anonymous 

If your relationships are set up right, you can use two dropdowns and a gallery to show your data. The items in the first one would be Managers, in the second one it would be Dropdown1.Selected.Projects and the Gallery would be Dropdown2.Selected.Milestones.

[Anonymous]

Thanks for the idea about the 2 dropdowns.  It's not exactly what I want to do, but may be the only choice.  I was playing around with it further and I started to get an error that basically says "multi-level one-to-many relationships is not supported" ... I guess the functions in the app don't have the ability to follow the relationship up more than one level is why I can't get it to work.  So I can see why the two levels of dropdowns or the LookUp function might be the way to go.  It should be supported though or at least some relatively straight forward solution as it's pretty common to have a data model that  has many levels.