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?

2 ACCEPTED SOLUTIONS

Accepted Solutions
PowerAddict
Super User
Super User

Hi @m_jeppesen 

Apologies for the delayed response. In the limited time I could spend on this (I really want to try it out more), I couldn't make the view work by itself. However, I wanted to share a solution that uses the view and solves the issue in a delegable way (no delegation issue). 

I have a button and its OnSelect is set to: 

Clear(colAccounts);
Clear(colFilteredAccounts);
ForAll(
    Distinct(
        Filter(
            Accounts,
            'Accounts (Views)'.'Custom View'
        ),
        Account
    ),
    Collect(
        colAccounts,
        Result
    )
);
ForAll(
    colAccounts,
    Collect(
        colFilteredAccounts,
        LookUp(
            Accounts,
            Account = Value
        )
    )
)

The first collection uses the filter that we both had created earlier. I am using the Accounts entity in my example and I created a view called Custom View. So the 1st collection stores the distinct GUIDs of accounts returned by the view. 

The next collection uses the 1st collection to store the complete account records. You can then use the 2nd collection as the data source for your dropdown. 

This code can be on say the OnVisible property of a screen or on the app's OnStart property. 

Let me know if this helps. And if I figure out the view filtering, I will definitely let you know. 

---
If you like this reply, please give kudos (Thumbs Up). And if this solves your problem, please mark this reply as a solution by selecting Accept as Solution. This makes it easier for others to find answers to similar questions. 

 

Thanks!

Hardit Bhatia

Microsoft Business Applications MVP

Microsoft Certified Trainer MCT

Blog | Twitter | LinkedIn | Facebook | YouTube  |  Email

View solution in original post

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

12 REPLIES 12
m_jeppesen
Helper I
Helper I

Found another 'issue indicator'. When designing the filtered view in CDS/Dataverse, the present of duplicates also shows up in the view designer on make.powerapps.

 

Below I have a "Parent" and a "Child" entity/table. I've made the view on the Parent entity, to filter parent records, so it should only show parent records, that have child records, but it doesn't work.

m_jeppesen_2-1607584579341.jpeg

m_jeppesen_0-1607584458789.jpeg

 

 

 

PowerAddict
Super User
Super User

Hi @m_jeppesen

I just tried to filter the list of Accounts to only show those that have related Contacts (one to many). This is how my View looks and it works perfectly fine: 

PowerAddict_0-1607586971724.png

If this doesn't work, can you try this? 

PowerAddict_1-1607587020133.png

Contact is the GUID of the related table. 

Let me know if these work. 

---
If you like this reply, please give kudos (Thumbs Up). And if this solves your problem, please mark this reply as a solution by selecting Accept as Solution. This makes it easier for others to find answers to similar questions. 

 

Thanks!

Hardit Bhatia

Microsoft Business Applications MVP

Microsoft Certified Trainer MCT

Blog | Twitter | LinkedIn | Facebook | YouTube  |  Email

Hi @PowerAddict 

Thank you for your response.

 

I'm sorry, but it get the same result, also in Account-Contact scenario.

 

Try to do the following:

1. Create one Account

2. Create two Contacts related to that Account

3. In the view designer (make.powerapps), after doing the query you've done, Click "OK" and look in the 'data previewer', in the view designer. I think you'll see that the Account you created in step 1. will show up twice - which is the same result you get, when using the view in a CanvasApp

 

Below in my image, you can see the same Account twice, in the

m_jeppesen_0-1607606713807.png

m_jeppesen_1-1607606754520.png

 

 

If you create 5 Contacts, all related to the Account, the Account will show up 5 times.

m_jeppesen_2-1607606945895.png

 

 

Kind regards

Magnus Jeppesen

 

 

I will definitely test this out and will come up with a solution. Stay tuned!

 

Thanks,

Hardit

I can assure you I'll stay tuned! Been struggling with this for a while now, so really interested to see if there is a solution for it 🙂 

m_jeppesen
Helper I
Helper I

Hi @PowerAddict 
Have you been able to find anything? 🙂

PowerAddict
Super User
Super User

Hi @m_jeppesen 

Apologies for the delayed response. In the limited time I could spend on this (I really want to try it out more), I couldn't make the view work by itself. However, I wanted to share a solution that uses the view and solves the issue in a delegable way (no delegation issue). 

I have a button and its OnSelect is set to: 

Clear(colAccounts);
Clear(colFilteredAccounts);
ForAll(
    Distinct(
        Filter(
            Accounts,
            'Accounts (Views)'.'Custom View'
        ),
        Account
    ),
    Collect(
        colAccounts,
        Result
    )
);
ForAll(
    colAccounts,
    Collect(
        colFilteredAccounts,
        LookUp(
            Accounts,
            Account = Value
        )
    )
)

The first collection uses the filter that we both had created earlier. I am using the Accounts entity in my example and I created a view called Custom View. So the 1st collection stores the distinct GUIDs of accounts returned by the view. 

The next collection uses the 1st collection to store the complete account records. You can then use the 2nd collection as the data source for your dropdown. 

This code can be on say the OnVisible property of a screen or on the app's OnStart property. 

Let me know if this helps. And if I figure out the view filtering, I will definitely let you know. 

---
If you like this reply, please give kudos (Thumbs Up). And if this solves your problem, please mark this reply as a solution by selecting Accept as Solution. This makes it easier for others to find answers to similar questions. 

 

Thanks!

Hardit Bhatia

Microsoft Business Applications MVP

Microsoft Certified Trainer MCT

Blog | Twitter | LinkedIn | Facebook | YouTube  |  Email

View solution in original post

Hi@PowerAddict 
No need to apologize 🙂

I've now been fiddling around with this for quite some time, and started to stare myself blind on it. You suggestion works like a charm! Thank you so much.

 

I really wanted to make this work, the way I think Microsoft intended - i.e. to use a View, but it looks like we are forced to find our own solution - like you did.

 

I've created a support ticket, so see if they have anything to say to the duplicated we get, when using a view.

I'll update this thread, if/when I hear something back.

m_jeppesen
Helper I
Helper I

@PowerAddict 
1 question though.

The first ForAll(), that collects all Account, don't that have a delegation limit?  So we would end up with only 2.000 records, if the delegation limit it increased from 500 to 2.000.

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 (2,578)