cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WallieWallie
Regular Visitor

Passing multiple listbox values as a filter into a gallery object

Hello Powerapps community,

 

I'm having an issue in which I can pass multiple, selected Listbox values as a variable towards a filter in a gallery object.

 

Example:

My Listbox has values like "2020-04", "2020-03", "2020-02", "2020-01", etc.... 
The gallery contains Tickets (from a IT servicedesk) in which these tickets have a date period in which they're reported. (e.g. Ticket CCX0001 was reported in date period "2020-03" and CCX0002 was reported in date period "2020-04".)

However the Listbox saves the values as a Table, to which I can't simply extract the values from. 
Here's how I store the values from the Listbox (in this instance called Listbox2) object:

 

 

OnSelect = UpdateContext({vSelected: ListBox2.SelectedItemsText.Value})

 

 

and to pass it to the Gallery I use:

 

 

Set(ReportingPeriod, Concat(ListBox2.SelectedItems.'Reporting Period', 'Reporting Period' & ";"))

 

 

 then to filter the gallery, I use the following syntax:

 

 

Filter('[DWH].[POWERAPP_INPUT]','Reporting Period' in ReportingPeriod)

 

 

 

The Database Source is a SQL Server database running on Azure.

2 REPLIES 2
gabibalaban
Super User
Super User

You can use directly ListBox.SelectedItems.{columnName} with IN function, it isn't necessary to use context / global variable.

 

I'm afraid that your formula:

Filter('[DWH].[POWERAPP_INPUT]','Reporting Period' in ReportingPeriod)

is treated as a non-delegable function and from here you might have unsatisfactory results.

https://docs.microsoft.com/en-us/connectors/sql/

For SQL the 'in' (substring) operator is limited supported. (Supported for ("string value" in <column>), but not for (<column> in "string value").  

 

To solve your issue, you can try to use the On Change property of listbox with a local collection in which you can keep the records from SQL and set the gallery items to this collection.

 

Hope it helps!

 

Hi @gabibalaban ,

 

First of all, thanks for the reply because I have been breaking my head over this issue.

 

Basically if I understand this correctly it means that:

The formula that I've tried to build ('Reporting Period' in Reporting Period) won't create an equivalent SQL statement such as below, because it cannot be delegated to SQL Server?

SELECT Reporting Period
FROM DWH_POWERAPP_INPUT
WHERE Reporting Period in ("2020-03", "2020-04")

The app uses multiple context/global variables from different dropdown objects, in order to pass these variable values through to the gallery. would it be smarter to create a local collection in order to populate the gallery with the filtered records? This in order to ensure that the multiple selected values from the Listbox as a filter return expected results.

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 (1,714)