cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
m_jeppesen
Helper I
Helper I

Only show parent records that has child records

Hi

 

I have a CDS/Dataverse datasource, with 2 tables, let's call them "Parent" and "Child". These are related in a 1:M relationship.

I want to display the Parent table in a dropdown, but only show records that have related child records.

 

So if we have 5 parent records, Parent 1,2,3,4,5 - and "Parent 4" don't have any related child records, then "Parent 4" should not be display in the dropdown.

 

Is there some way of doing this with a query inside PowerApps, without the use of CDS/Dataverse views? - since I keep running into an issue in CDS/Dataverse, where doing the query works in Dynamics, but when using the view in PowerApps, the view returns the same 'parent' record x-amount of time, depending on how many child records are in the system.

 

So can this be done in a delegatable way?

12 REPLIES 12
PowerAddict
Super User
Super User

If you do just this, ClearCollect(colAccounts, Accounts) then you will run into delegation. This one will not. 

Also, as a follow-up to the other topic, what's weird is that if you use the same view in a model-driven app, it works perfectly fine. So it just doesn't work in the maker portal and canvas apps. I will investigate this more and report it as a bug. 

 

Thanks!

Hardit Bhatia

Microsoft Business Applications MVP

Microsoft Certified Trainer MCT

Blog | Twitter | LinkedIn | Facebook | YouTube  |  Email

PowerAddict
Super User
Super User

Hi @m_jeppesen , 

So I figured out why the view wasn't working for us in the maker portal and in the canvas app, although it was working perfectly fine in a model-driven app. The query was missing the "distinct" condition, not sure if that's by design or not. And when a model-driven app uses a view, it adds the "distinct" condition to any existing view filters/queries. 

 

That being said, you can "fix" it by using a tool called XRMToolBox. Here are a few steps (I won't get into the details so as to not overwhelm you with something that you may not use):

1. Download the XRM Tool Box, just google it, its free

2. Add the View Designer tool to it

3. Connect to your environment 

4. Select the entity and then your view

5. Click on Edit Query

6. Select the Distinct checkbox in the left nav

7. Save and Publish your view

8. You will see that the results now show up correctly within this tool

9. Go and refresh the data source in your canvas app, the records will appear correctly

I know this sounds too complicated but it's not. Let me know if you want to try this out and have any questions!

 

Thanks!

Hardit Bhatia

Microsoft Business Applications MVP

Microsoft Certified Trainer MCT

Blog | Twitter | LinkedIn | Facebook | YouTube  |  Email

View solution in original post

Thank you for your concern @PowerAddict, but don't know how I would fulfill my job without XML Tool Box 😉
That being said, haven't used View Designer that much, so thank you - and it worked like a charm!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,782)