cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Serviced2000
Helper III
Helper III

Filtering Using Multiple Relationships

Hi Everyone,

 

I have been building an app and have managed to overcome most of the technical issues (although with some bodges - some things aren't as straightforward as they seem to be on YouTube!). I still have one thing left to work out and I can't for the life of me work out how to do this.

 

I have a data structure in CDS that looks a bit like this:

 

Approval (Main Table) --> Placement --> Placement Type (Choice)

 

And I need to be able to filter the approval table based on the Placement Type but I'm told by Canvas that traversing multi level relationships is not supported. Does anyone know a way I might be able to do this? 

 

Thanks!

8 REPLIES 8
CNT
Community Champion
Community Champion

@Serviced2000 

I there a relationship between the Approval and Placement tables?

If so what kind of relationship?

Is Placement Type a Choices column in the Placement Table?

cchannon
Solution Sage
Solution Sage

Create an Advanced Find view in the Model Driven App experience. Filtering on attributes multiple jumps away is just fine in Fetch, Canvas just limits your ability to do it at runtime.

 

So, create the view, then when you pick your datasource, make sure you use the filtered view you created.

Yes, Approval and Placement are related by the 'Regarding' column (polymorphic relationship?). Placement Type is a global Choice which is referenced as a column in the Placement table.

Hi @cchannon 

 

This is a great idea and would certainly be the holy grail for getting all this data across from the various tables, but I have tried this (I found it on a YouTube video) and created a view called 'Test' with a couple of the fields I need in it (I've highlighted one called PlacementID).

 

Serviced2000_0-1627867275400.png

And I can see the view in Canvas, no problem...

Serviced2000_1-1627867354598.png

But when I look for the PlacementID field to add it to the Gallery item, it's nowhere to be seen...

Serviced2000_2-1627867459009.png

The field display name is 'Placement ID' and the column name is 'mercury_placementidcode' and you can see from the screenshot that neither appear in the list.

 

Views like this would solve a whole raft of problems for me if I can get them working and it is perfectly possible I missed a step somewhere so if there are any further pointers you can give me that would be awesome!

 

 

Check the column properties. There is a lesser-known property called "searchable" which if set to false causes the column to never appear in Views or Advanced Find.

Thanks @cchannon 

 

Really sorry, but that doesn't seem to be the problem. Checking the column properties for Placement ID, for example, it looks like it's set to 'Searchable'. This is within the CDS table though - should I be looking somewhere else?

 

Serviced2000_0-1627941048148.png

 

Serviced2000
Helper III
Helper III

I think I should probably mention that some of my fields are from different tables, so in the view :

Serviced2000_0-1627954764959.png

The view is of the Placement Approval table and the PlacementID and Type columns are from the Placement table.

 

Hope this helps!

Haven't dealt with that before, but in that case the answer might be as simple as adding the Placement Table to your data sources.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,965)