cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Memorable Member
Memorable Member

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!

 

Highlighted

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (13,945)