Filter using polymorphic fields - running into data limit issues

[basa314]

I appear to be running into a data row limit with a canvas power app using CDS as a result of a non-delegable query using a polymorphic lookup that I don't know how to avoid using.  I'd be very grateful for anyadvice how to solve this.

In my Power App, I first have a gallery (=Gallery 1) which lists all the Accounts in my D365 instance from which the user selects one.

I then want to display all the Contacts assigned to the selected Account in a second gallery.

The mapping of Contact to Account is maintained through this field on Contact:

- Display Name="Company Name"

- Name = parentcustomerid

- Data Type = Customer

With this data type I appear to be in polymorphic territory so I've been trying to use this filter:

Filter(Filter(Contacts, 'Contacts (Views)'.'Active Contacts',IsType('Company Name', [@Accounts] )), AsType('Company Name', [@Accounts] ).Account=Gallery1.Selected.Account)

The problem is that this lists the Contacts for some Accounts and not for others. 

I think I've proven it's a data row limit problem as I can display the Contacts for more Accounts by increasing the data row limit from 500 to 2000 in Power Apps settings.   I can't find it in the documentation but I can only assume that AsType is not delegable in a filter??

Increasing the data row limit can't be my final solution as I've 10's of thousands of Accounts and Contact.

Is there a solution for this?  It would be great if I could simply do the filter on the guid in the parentcustomerid field but I can't seem to find a way to do that.

[alrez]

Hi,

Looking through the documentation and your verbalism of your case, it does appear that the issue is related to the data row limit for non-delegable queries as specified in the article below; I haven't been able to find any other workarounds for this other then manually increasing the limits however Microsoft Customer Support might be better at locating a solution that could work for your specific situation.

https://powerapps.microsoft.com/en-us/blog/powerapps-data-row-limit-for-non-delegable-queries/

If you would like to create a ticket with Microsoft Customer support here is a link on how to do so: https://docs.microsoft.com/en-us/power-platform/admin/get-help-support

Regards,

Alex

-------

Community Support Team _ Alex Rezac
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[basa314]

Thanks Alex. 

I've raised the question with Microsoft support and their investigations are ongoing.

Maybe a simpler way of posing my question is: "In a CDS with 10's of thousands of Accounts and 10's of thousands of Contacts but typically <100 Contacts assigned to each Account, how do you configure a gallery in a canvas Power App to reliably list the Contacts for a given Account?"

Thanks again, Jonathan

[basa314 · Accepted solution]

Microsoft support kindly has given me the answer here (thank you Microsoft):

The answer is simple in that I'm able to filter by comparing the record references and selected records themselves rather than resolving to particular fields, as in:

Filter(Contacts, 'Contacts (Views)'.'Active Contacts', 'Company Name' = Gallery1.Selected) 

Goodness knows why I didn't try this before but it fixed my problem and the delegation warning has now gone.  All fixed.  Thanks all.

[Tejaswini_Tambe]

u answer is so helpful for my issue . thanks