Only show parent records that has child records

[m_jeppesen]

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?

[m_jeppesen]

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.

[PowerAddict]

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: 

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

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

[m_jeppesen]

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

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

Kind regards

Magnus Jeppesen

[PowerAddict]

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

Thanks,

Hardit

[m_jeppesen]

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]

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

[PowerAddict · Accepted solution]

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

[m_jeppesen]

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]

@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.