How to avoid delegation warning using SQL DB as datasource

(0) Share

Report

Posted on by kamil_k

I want to filter gallery based on selection made in another gallery.

Data Source is SQL Database.

In Gallery2, I have selected ProductCategoryID's which don't have ParentProductCategoryID.

In another gallery I tried to use below function but I get delegation warning due to IN operator.

Filter(
 'SalesLT.Product';
 ProductCategoryID in Filter(
 'SalesLT.ProductCategory';
 ParentProductCategoryID = Gallery2.Selected.ProductCategoryID
 ).ProductCategoryID
)

SalesLT.Product table:

ProductID	ProductCategoryID
555	5
556	5
666	6
667	6
777	7
778	7
888	8
889	8
999	9
1000	10
1001	10
1100	11
1101	11
1200	12
1201	12

SalesLT.ProductCategory table:

ProductCategoryID	ParentProductCategoryID
1	NULL
2	NULL
3	NULL
4	NULL
5	1
6	1
7	2
8	2
9	3
10	3
11	4
12	4

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (0)

Sort by

WarrenBelz 155,344 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Hi @kamil_k ,

Firstly try this

With( { _Data: Filter( 'SalesLT.ProductCategory'; ParentProductCategoryID = Gallery2.Selected.ProductCategoryID ) }, Filter( 'SalesLT.Product'; ProductCategoryID in _Data.ProductCategoryID ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
kamil_k 14 on at

Like (0)

Report
Copy link

Link copied!

Hi @WarrenBelz,
Thank you but unfortunately, I'm still getting this warning:

Was this reply helpful? Yes No
WarrenBelz 155,344 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

@kamil_k ,

My understanding is that the in operator is Delegable when querying a Text field in SQL. It appears that it does not like the pre-filter. Try setting a Variable to the first part and see if that works. If not, there is really no solution here,

Was this reply helpful? Yes No
kamil_k 14 on at

Like (0)

Report
Copy link

Link copied!

Thanks @WarrenBelz
Exactly, in operator is a problem here. I found an info that it's delegable to sql server but only partially
https://learn.microsoft.com/en-us/connectors/sql/#power-apps-functions-and-operations-delegable-to-sql-server

I solved my problem creating a view in SQL server where I joined additional column to the Product table so I can filter it with delegable functions only.
Thank you for your time.

Was this reply helpful? Yes No