Unanswered

Passing multiple listbox values as a filter into a gallery object

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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.

Categories:

Building Power Apps

All responses (2)

Answers (0)

Sort by

CU-18081211-6 9,272 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
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!
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
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.