Filters Collections and SQL

(0) Share

Report

Posted on by Mark Jones111

Hi,

I'm pretty new to Power Apps and I'm struggling with getting a filter working that is using a collection I've built.

I'm trying to understand if it's possible to delegate the query to SQL whilst using a collection as it returns an error when I try. If I don't delegate to SQL I'm only getting partial results as expected.

I am attempting to build a CPQ within Power Apps and I'm currently working on the Filter functionality, at this point in time the end-user would be selecting product attributes(which upon selection I'm adding to a collection) and then applying a filter on products that have the selected attributes.

The Expression that errors are below:

Filter('[dbo].[Product_ProductAttributes]', ProduAttCollection.AttTypeValueValue in ProductAttributeValue)

The error returned is - Invalid argument type. Cannot use Table values in this context.

Now if I reverse the logic within the expression the query returns but I'm hitting the delegation issue so only receiving a partial result set:

Filter('[dbo].[Product_ProductAttributes]', ProductAttributeValue in ProduAttCollection.AttTypeValueValue)

Any help would be greatly appreciated.

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (16)

Answers (0)

Sort by

eka24 20,925 on at

Like
a
(0)

Report
Copy link

Link copied!

Using SQL will allow to filter without delegation issues except dates. Even that there is a workaround. The issue might be the in function:
https://docs.microsoft.com/en-us/connectors/sql/

I suggest you insert a dropdown and rather filter based on the Dropdown like:
Filter('[dbo].[Product_ProductAttributes]', ProductAttributeValue = Dropdown1.Selected.Value)
------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Was this reply helpful? Yes No
Mark Jones111 14 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,

Thanks very much for the response, I'm not sure I can just base it off of a dropdown as the end-user will be making multiple selections from a Gallery/ListBox.

For example, I currently have a DropDown allowing the user to select a "Product Group" Then a ComboBox that is filtered upon that initial DropDown which will only show "Product Attributes" related to the chosen Product Group. I then have a Gallery which is filtered to display the "Product Attributes" chosen within the ComboBox, the Gallery contains a ListBox which displays Distinct "Attribute Values".

For example, a user may select 5 "Product Attributes" to filter on and then may choose x number of Distinct values to filter on and at this point I'd query SQL based upon the "ProductAttributes" & "ProductValues" to return a list of appropriate "Products" based on the filter criteria chosen.

Thanks

Was this reply helpful? Yes No
eka24 20,925 on at

Like
a
(0)

Report
Copy link

Link copied!

Well noted. So the approach you want is to check an filter items in SQL which are also in a Collection?
If that is not case please clarify
------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Was this reply helpful? Yes No
Mark Jones111 14 on at

Like
a
(0)

Report
Copy link

Link copied!

Yeah pretty much, I want to create a collection and then compare the values of the collection(via an IN clause) against a table in SQL.

The table in SQL contains more than 2000 rows.

Thanks for your help.

Was this reply helpful? Yes No
eka24 20,925 on at

Like
a
(0)

Report
Copy link

Link copied!

Are AttTypeValueValue and ProductAttributeValue the same, because the formula seem fine.
Filter('[dbo].[Product_ProductAttributes]', ProductAttributeValue in ProduAttCollection.AttTypeValueValue)

Please can you show the formula for the collection.
------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Was this reply helpful? Yes No
Mark Jones111 14 on at

Like
a
(0)

Report
Copy link

Link copied!

Yes those values should be identical, I'm visualising the collection on-screen to debug and I can see the values should match the SQL DB values.

This is the code that is creating the collection:

Collect(ProduAttCollection,{AttTypeFilterValue:ThisItem.Result, AttTypeValueValue:ListBox2.Selected.Result})

It's on the OnSelect Action of a ListBox

If I reverse the collection and the SQL source in the expression, like below. the expression works but I get the delegation warning.

Filter('[dbo].[Product_ProductAttributes]', ProduAttCollection.AttTypeValueValue in ProductAttributeValue)

Was this reply helpful? Yes No
Mark Jones111 14 on at

Like
a
(0)

Report
Copy link

Link copied!

Just bumping this in the hopes that someone can lend a hand as it's become a blocker to the project.

Thanks

Was this reply helpful? Yes No
WarrenBelz 156,382 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@Jonesy81 ,
I will throw into something if it helps
Should possibly the code be on the OnChange of the control rather than the OnSelect ?
ThisItem normally refers to a Record - if referring to a Control, should this be YourControlName.Selected.Result ?

Was this reply helpful? Yes No
WarrenBelz 156,382 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Jonesy81 ,
Just checking if you got the result you were looking for on this thread. Happy to help further if not.

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.

Was this reply helpful? Yes No
Mark Jones111 14 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz Hi, Thanks very much for the offer.

I've made the selected change to the collection, although the items were already being added to the collection properly:

Collect(ProduAttCollection,{AttTypeFilterValue:ProductAttributeFilterGallery.Selected.Title6, AttTypeValueValue:ListBox2.Selected.Result})

I've also changed the code to be on the OnChage event.

My issue seems to be, delegation.

For example, the below code works but I'm getting delegation warning and therefore I'm only getting a partial result set:

Filter('[dbo].[Product_ProductAttributes]', ProductAttributeValue in ProduAttCollection.AttTypeValueValue)

When I place the SQL source on the right-hand side of the query(which I believe is how I signal that I want to delegate the query) the expression errors:

Filter('[dbo].[Product_ProductAttributes]', ProduAttCollection.AttTypeValueValue in ProductAttributeValue)

Error received - Invalid argument type. Cannot use table values in this context.

Any help would be greatly appreciated as this is blocking me from making any further progress on the project.

Many Thanks

Was this reply helpful? Yes No