Answered

Search Multiple List Columns based on Multiple Checkboxes

(0) Share

Report

Posted on by Poweruser1111

I have a list with columns Col1 to Col9, and each column contains comma separated city names (City1 to City5).

I've created an app that needs to filter which columns I search through and which cities are searched for in the columns and display them in a gallery.

So CityCheckbox1, CityCheckbox2 + ColCheckbox1 looks for list entries that might have city 1 OR city 2 in column 1.

Similarly CityCheckbox1, CityCheckbox2 + ColCheckbox1, ColCheckbox2 would be city 1 OR city 2 in Col1 AND Col2.

My first thought was to keep appending the cities to a collection and search, but I don't know if searching beyond that is possible.

My current formula (which only works for once city at a time) is:

Search(

AddColumns(

Filter(SourceList,
If(ColCheckbox1.Checked ,If(CityCheckbox1.Checked, "City1", CityCheckbox2.Checked, "City2", false) in Col1, true)

,"Searchby", Sourcelist.title, "Searchby1", Sourcelist.title2),

Searchbox.Value, "Searchby1", "Searchby2")

Repeated for each Colcheckbox.

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Sort by

Verified answer

BCBuizer 22,833 Super User 2026 Season 2 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Poweruser1111 ,

I haven't tested this, but my first try would be:

Search( AddColumns( Ungroup( ForAll( Filter( [ {FilterBy: CityCheckbox1.Checked, Value "City1"}, {FilterBy: CityCheckbox2.Checked, Value "City2"}, {FilterBy: CityCheckbox3.Checked, Value "City3"}, {FilterBy: CityCheckbox4.Checked, Value "City4"}, {FilterBy: CityCheckbox5.Checked, Value "City5"} ], FilterBy ) As SelectedCities, { TempTable: Filter( SourceList, (!ColCheckbox1.Checked || SelectedCities.Value in Col1), (!ColCheckbox2.Checked || SelectedCities.Value in Col2), (!ColCheckbox3.Checked || SelectedCities.Value in Col3), (!ColCheckbox4.Checked || SelectedCities.Value in Col4), (!ColCheckbox5.Checked || SelectedCities.Value in Col5), (!ColCheckbox6.Checked || SelectedCities.Value in Col6), (!ColCheckbox7.Checked || SelectedCities.Value in Col7), (!ColCheckbox8.Checked || SelectedCities.Value in Col8), (!ColCheckbox9.Checked || SelectedCities.Value in Col9) }) ), "TempTable", ), "Searchby", Sourcelist.title, "Searchby1", Sourcelist.title2 ), Searchbox.Value, "Searchby1", "Searchby2" )

In layman's terms, this first checks which city checkboxes are checked and "collects" the values in SelectedCities. Then for each item in Selected cities a filter is applied, based on the selected Col checkboxes, and that result is "collected" in nested tables in the TempTable column. These are then ungrouped to merge all of the rows in those nested tables. Finally the AddColumns and Search criteria are applied.

A note though: whilst this looks good on paper, in reality the performance, especially with larger numbers of columns/rows involved, will be pretty bad with long response times. I suggest you look into what Delegation is in Power Apps and adjust your data model accordingly to avoid N:N relationships.

Was this reply helpful? Yes No
Poweruser1111 7 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks a lot, that worked just like I needed. Still very daunting to look at though.

Was this reply helpful? Yes No