cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chris-Hockey
Frequent Visitor

Filter Gallery With Search & Multiple Dropdowns from Sharepoint List

Hello All,


I have a Sharepoint list built (attached: Excel Query from Sharepoint List) that runs to a powerapp (attached: BPI DataBase Q&A PowerApp V1) where I am looking to create a dynamic search (named: textinput1) and filter from combo boxes (named: combobox1, combobox1_1, combobox1_2, combobox1_3, combobox1_4,  combobox1_5). 

 

Image-1.png

My basic formula for the search and sort I have is: SortByColumns(Search('Database Q&A''s (SP List)',TextInput1.Text,"Question"),"Question").

 

Tried watching a few videos to get the comboboxes to work as well with this but no dice:

 

SortByColumns(
Search(
Filter(
isblank(combobox1.selecteditems.value) // isempty (combobox1.selecteditems.value) // closehold in (combobox1.selecteditems)
isblank(combobox1_1.selecteditems.value) // isempty (combobox1_1.selecteditems.value) // Commitee Requestor in (combobox1_1.selecteditems)
isblank(combobox1_2.selecteditems.value) // isempty (combobox1_2.selecteditems.value) // Topics in (combobox1_2.selecteditems)
isblank(combobox1_3.selecteditems.value) // isempty (combobox1_3.selecteditems.value) // keywords in (combobox1_3.selecteditems)
isblank(combobox1-4.selecteditems.value) // isempty (combobox1_4.selecteditems.value) // requestor in (combobox1_4.selecteditems)
isblank(combobox1_5.selecteditems.value) // isempty (combobox1_5.selecteditems.value) // mission area in (combobox1_5.selecteditems)
'Database Q&A''s (SP List)',
TextInput1.Text,"Question"),
"Question")

 

Note: From the Sharepoint list I have question displayed in the gallery and the other fields are present in the gallery as well but sent to back with clear font so you don't see them.

 

Any help would be appreciated to get the app to function ~ Thanks.

3 ACCEPTED SOLUTIONS

Accepted Solutions

ok that makes alot of sense -- I dug into the column list and was able to figure out the code based on your response. Final code was: 

 

SortByColumns(
Search(
Filter('Database Q&A''s (SP List)',
IsBlank(ComboBox1.SelectedItems.Value) || IsEmpty(ComboBox1.SelectedItems) || Closehold.Value in ComboBox1.Selected.Value,
IsBlank(ComboBox1_1.SelectedItems.Value) || IsEmpty(ComboBox1_1.SelectedItems) || 'Committee Requestor'.Value in ComboBox1_1.Selected.Value,
IsBlank(ComboBox1_2.SelectedItems.Value) || IsEmpty(ComboBox1_2.SelectedItems) || Concat(Topics, Value, ",") in ComboBox1_2.Selected.Value,
IsBlank(ComboBox1_3.SelectedItems.Value) || IsEmpty(ComboBox1_3.SelectedItems) || Concat(Keywords, Value, ",") in ComboBox1_3.Selected.Value,
IsBlank(ComboBox1_4.SelectedItems.Value) || IsEmpty(ComboBox1_4.SelectedItems) || Requestor.Value in ComboBox1_4.Selected.Value,
IsBlank(ComboBox1_5.SelectedItems.Value) || IsEmpty(ComboBox1_5.SelectedItems) || Concat('Mission Area', Value, ",") in ComboBox1_5.Selected.Value
),
TextInput1.Text,"Question")
,"Question")

 

 

~ Thanks for all your help!

View solution in original post

zaphod88
Responsive Resident
Responsive Resident

Can you try turning the three Concat() comboboxes turning around in the following way and see if it works as expected?

IsBlank(ComboBox1_3.SelectedItems.Value) || IsEmpty(ComboBox1_3.SelectedItems) || ComboBox1_3.Selected.Value in keywords

View solution in original post

Chris-Hockey
Frequent Visitor

Wow so simple and awesome quick fix ~ Thanks so Much !!!

 

Updated Code:

 

SortByColumns(
Search(
Filter('Database Q&A''s (SP List)',
IsBlank(ComboBox1.SelectedItems.Value) || IsEmpty(ComboBox1.SelectedItems) || Closehold.Value in ComboBox1.Selected.Value,
IsBlank(ComboBox1_1.SelectedItems.Value) || IsEmpty(ComboBox1_1.SelectedItems) || ComboBox1_1.Selected.Value in 'Committee Requestor'.Value,
IsBlank(ComboBox1_2.SelectedItems.Value) || IsEmpty(ComboBox1_2.SelectedItems) || ComboBox1_2.Selected.Value in Concat(Topics, Value, ","),
IsBlank(ComboBox1_3.SelectedItems.Value) || IsEmpty(ComboBox1_3.SelectedItems) || ComboBox1_3.Selected.Value in Concat(Keywords, Value, ","),
IsBlank(ComboBox1_4.SelectedItems.Value) || IsEmpty(ComboBox1_4.SelectedItems) || ComboBox1_4.Selected.Value in Requestor.Value,
IsBlank(ComboBox1_5.SelectedItems.Value) || IsEmpty(ComboBox1_5.SelectedItems) || ComboBox1_5.Selected.Value in Concat('Mission Area', Value, ",")
),
TextInput1.Text,"Question")
,"Question")

View solution in original post

12 REPLIES 12
zaphod88
Responsive Resident
Responsive Resident

Try

SortByColumns(
 Filter('Database Q&A''s (SP List)',
   TextInput1.Text in Question, 
   closehold in ComboBox1.SelectedItems.Value, 
   'Commitee Requestor' in ComboBox1_1.SelectedItems.Value, 
   Topics in ComboBox1_2.SelectedItems.Value, 
   keywords in ComboBox1_3.SelectedItems.Value, 
   requestor in ComboBox1_4.SelectedItems.Value, 
   'mission area' in ComboBox1_5.SelectedItems.Value),
"Question"
)

~ Thanks for the response, but it's a big Negative.

 

Code appears to be valid, but gallery comes up blank and when selecting any dropdowns remains blank.

 

Image-2.png

 

Image-3.png

Try to set the DefaultSelectedItems property the same as the items property for all comboboxes. Then by default, all possible filters are chosen and your user can filter the items down.
You could also try using an OR filter:

 

 

SortByColumns(
 Filter('Database Q&A''s (SP List)',
   Or(TextInput1.Text in Question, 
   closehold in ComboBox1.SelectedItems.Value, 
   'Commitee Requestor' in ComboBox1_1.SelectedItems.Value, 
   Topics in ComboBox1_2.SelectedItems.Value, 
   keywords in ComboBox1_3.SelectedItems.Value, 
   requestor in ComboBox1_4.SelectedItems.Value, 
   'mission area' in ComboBox1_5.SelectedItems.Value)),
"Question"
)

 

 

But I guess what you want is something between AND and OR filter so that your users can choose any combobox to filter it down. AFAIK, to achieve that, you either need giant If() clauses for all possible variations of comboboxes or IMHO 11 collections at least to represent the gallery and the 5 comboboxes (to filter by items that are selected and to re-collect them to the gallery if an item is re-chosen in a combobox).

Chris-Hockey
Frequent Visitor

Yeah the idea is that the gallery would show all the items in the list and if a user typed in the search and/or selected items in the dropdown comboboxes, it would narrow the list.

 

I got the idea from watching the youtube video "PowerApps Multiple Filters on Gallery - by Reza Dorrani" right about at 8 minutes he uses the search and combobox.

zaphod88
Responsive Resident
Responsive Resident

Ah okay. That's an interesting approach indeed. So try the following: 

SortByColumns(
 Search(
   Filter('Database Q&A''s (SP List)',
      IsBlank(ComboBox1.SelectedItems) || IsEmpty(ComboBox1.SelectedItems) || closehold in ComboBox1.SelectedItems.Value, 
     IsBlank(ComboBox1_1.SelectedItems) || IsEmpty(ComboBox1_2.SelectedItems) || 'Commitee Requestor' in ComboBox1_1.SelectedItems.Value, 
     IsBlank(ComboBox1_2.SelectedItems) || IsEmpty(ComboBox1_2.SelectedItems) || Topics in ComboBox1_2.SelectedItems.Value, 
     IsBlank(ComboBox1_3.SelectedItems) || IsEmpty(ComboBox1_3.SelectedItems) || keywords in ComboBox1_3.SelectedItems.Value, 
     IsBlank(ComboBox1_4.SelectedItems) || IsEmpty(ComboBox1_4.SelectedItems) || requestor in ComboBox1_4.SelectedItems.Value, 
     IsBlank(ComboBox1_5.SelectedItems) || IsEmpty(ComboBox1_5.SelectedItems) || 'mission area' in ComboBox1_5.SelectedItems.Value),
  TextInput1.Text; "Question"),
"Question"
)

~ Thanks for the quick responses, but another negative {sad face}

 

I made a  short 2 minute video of what is happening and uploaded to youtube >  Video Walkthru Error - Filter Gallery With Search & Multiple Dropdowns from Sharepoint List 

 

 

zaphod88
Responsive Resident
Responsive Resident

There are multiple problems about your column names and types. Please take a screenshot of the column list in list preferences in Sharepoint Online which shows all relevant columns (i.e. all those in the code snippet I gave above).

ok that makes alot of sense -- I dug into the column list and was able to figure out the code based on your response. Final code was: 

 

SortByColumns(
Search(
Filter('Database Q&A''s (SP List)',
IsBlank(ComboBox1.SelectedItems.Value) || IsEmpty(ComboBox1.SelectedItems) || Closehold.Value in ComboBox1.Selected.Value,
IsBlank(ComboBox1_1.SelectedItems.Value) || IsEmpty(ComboBox1_1.SelectedItems) || 'Committee Requestor'.Value in ComboBox1_1.Selected.Value,
IsBlank(ComboBox1_2.SelectedItems.Value) || IsEmpty(ComboBox1_2.SelectedItems) || Concat(Topics, Value, ",") in ComboBox1_2.Selected.Value,
IsBlank(ComboBox1_3.SelectedItems.Value) || IsEmpty(ComboBox1_3.SelectedItems) || Concat(Keywords, Value, ",") in ComboBox1_3.Selected.Value,
IsBlank(ComboBox1_4.SelectedItems.Value) || IsEmpty(ComboBox1_4.SelectedItems) || Requestor.Value in ComboBox1_4.Selected.Value,
IsBlank(ComboBox1_5.SelectedItems.Value) || IsEmpty(ComboBox1_5.SelectedItems) || Concat('Mission Area', Value, ",") in ComboBox1_5.Selected.Value
),
TextInput1.Text,"Question")
,"Question")

 

 

~ Thanks for all your help!

View solution in original post

Chris-Hockey
Frequent Visitor

zaphod88,

 

So an interesting issue came up when testing. Some of the items in the Sharepoint list are blank (I.E. Column: Keywords) and when searching on items in keywords, if the column is blank it is not filtering out the items. ¯\(ツ)/¯

 

SP-1.png

SP-3.png

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (1,912)