Filter gallery by checkboxes and search bar

[teaful]

I'm new to Power Apps. We are a Non-profit Assistance Dog training facility going paperless. (from 1980's to 2020 at warp speed)

I have a gallery with a Sharepoint data source that I want to filter down by checkboxes and by name via search box (in any order the user chooses). Really struggling and can't seem to find any data on the subject, but from what I've read thus far, I will need to create a collection, and then each box checked or search item would re-write the collection?

I have checkboxes for Hearing Dog (HD), Home Hearing Dog (HHD), Autism Assistance Dog (AAD) and Facility Dog (FD) and State.

I have three separate screens. One for our clients, one for our dogs, and one for our Teams (a dog that's been placed with a client after training has been completed.)

Each screen will need a similar filter.  In working with Clients first, I have a gallery (the selected record shows in a form), the checkboxes (not working yet) and the search field (works). I just can't figure out how to make the checkboxes and search field work together. Any help would be appreciated. 

Example need: staff can filter by checkboxes HD & HHD in Oregon. The gallery returns a list of results of only HD or HHD clients in only Oregon, of which they can then do a name search by any criteria in the record. Of course clicking on any one result shows the data in the associated form. 

[VDS_Mantra]

Hi, 

Use the below one it will help you.

For checkbox value, you need to pass selected value text in one variable and then use into the filter

if(Checkbox1.value = true, set(VCheckboxtext,Checkbox1.text),if(Checkbox2.value = true,set(VCheckboxtext,Checkbox1.text),set(VCheckboxtext,""))

Filter(Source Name, VCheckboxtext in DogCategory && inpSearchbox.Text in SentBy)

I would suggest use the Radio button instead of checkbox. 

Kindly change the parameter name as per your data set. 

I hope it will help you. 

[teaful]

VDS_Mantra, thank you for providing a solution. I'm afraid I do not know where to put those formulas though. Can you provide more detail?

With radio buttons, you can only choose one. I have the need to choose multiple sometimes. 

[v-yutliu-msft]

Hi @teaful ,

Do you want to filter based on 5 check boxes and one textinput?

Could you tell me:
1)which column do you want to filter based on by these check boxes? column name and data type?

2)which column do you want to filter based on by the textinput? column name and data type?

I assume that you want to filter fieldname1(single choice type) by using these checkboxes,

filter fieldname1(text type) by using the textinput.

Then please set like this:
1)checkboxes' Text:

"Hearing Dog (HD)"

"Home Hearing Dog (HHD)"

"Autism Assistance Dog (AAD)"

"Facility Dog (FD)"

"State"

2)the gallery's Items:

Filter(listname,
If(Checkbox1.Value,"Hearing Dog (HD)"=fieldname1.Value,false)||
If(Checkbox2.Value,"Home Hearing Dog (HHD)"=fieldname1.Value,false)||
If(Checkbox3.Value,"Autism Assistance Dog (AAD)"=fieldname1.Value,false)||
If(Checkbox4.Value,"Facility Dog (FD)"=fieldname1.Value,false)||
If(Checkbox5.Value,"State"=fieldname1.Value,false),
If(IsBlank(TextInput1.Text),true,
    TextInput1.Text in fieldname2)
)
 

Best regards,

[teaful]

Phoebe Liu Thank you for your detail. I appreciate it!

To answer your questions:

1. There will be multiple columns that I want to filter by via checkbox. One column (in the example provided previously) is a single line of text called 'Client Type' containing HD, HHD, AAD or FD. Another column that will be a checkbox will be 'Successor' which has a value of yes or no in the single line of text column, also 'Staff or Ambassador' which is single line of text column (a checkbox for each maybe?), and 'State' which is single line of text column (a checkbox for each based on what exists in the column.) Would State be better off in a gallery of checkboxes by itself based on what States exist in the column? I tried to do this using Distinct, but it didn't work) Same question for 'Waitlisted in Months'. There might be other checkboxes included later that will be single lines of text as well, but they are still TBD. 

2. The text input box will filter by 'FirstName', 'LastName', 'Client Number', 'Zip', 'email', or 'Phone'. Q: Will a search box work if someone put in something like: "Johnson 97586"? 

I need the filter results to show records that include ALL of the items checked or chosen, NOT JUST ANY. (similar to Amazon's metadata checkboxes down the left column) The more details you check, the smaller the list gets because it includes ALL of your choices.

Thank you for your expertise.

[v-yutliu-msft · Accepted solution]

Hi @teaful ,

Do you want to filter based on some checkboxes and a searchbox?

Could you tell me  what does "Johnson 97586" represent?  'FirstName'+'Client Number'? I assume that yes.

Do you mean that all your fields are text type? include 'Client Number' and 'Phone' field? I assume that yes.

If so, I've made a similar test for your reference:

//test831 is my listname, please replace that

1/checkbox1-Checkbox1_6: 

for these three columns:'Client Type' containing HD, HHD, AAD or FD  (if you do not choose any related checkboxes, you will not filter based on 'Client Type' column)

                                        'Successor' containing yes,no(if you choose related checkbox, you will filter  'Successor' based on "yes" value. if you not choose related checkbox, you will filter  'Successor' based on "no" value.)

                                        'Staff or Ambassador' containing staff,Ambassador (if you do not choose any related checkboxes, you will not filter based on 'Staff or Ambassador' column)

2/gallery1 for filtered State column(based on checkboxes) with no-repeated value

set gallery1's Items:

Distinct(
         If(
           !Checkbox1.Value&&!Checkbox1_1.Value&&!Checkbox1_2.Value&&!Checkbox1_3.Value&&
           !Checkbox1_5.Value&&!Checkbox1_6.Value,
//not choose checkboxes for 'Client Type'  and 'Staff or Ambassador'
           Filter(test831,If(Checkbox1_4.Value,Successor="yes",Successor="no")),
//filter on based on Successor
            !Checkbox1.Value&&!Checkbox1_1.Value&&!Checkbox1_2.Value&&!Checkbox1_3.Value&& 
            (Checkbox1_5.Value||Checkbox1_6.Value),
//not choose checkboxes for 'Client Type', choose check box for 'Staff or Ambassador'
            Filter(test831,If(Checkbox1_4.Value,Successor="yes",Successor="no"),
                           If(Checkbox1_5.Value,'Staff or Ambassador'="Staff")||If(Checkbox1_6.Value,'Staff or Ambassador'="Ambassador")),
//filter based on 'Staff or Ambassador' and Successor
             (Checkbox1.Value||Checkbox1_1.Value||Checkbox1_2.Value||Checkbox1_3.Value)
                &&!Checkbox1_5.Value&&!Checkbox1_6.Value,
//choose checkboxes for 'Client Type', not choose check box for 'Staff or Ambassador'
              Filter(test831,If(Checkbox1.Value,'Client Type'="CHD")||If(Checkbox1_1.Value,'Client Type'="HAD")||If(Checkbox1_2.Value,'Client Type'="FD")||If(Checkbox1_3.Value,'Client Type'="AAD"),
If(Checkbox1_4.Value,Successor="yes",Successor="no")),
filter based on 'Client Type' and Successor
              (Checkbox1.Value||Checkbox1_1.Value||Checkbox1_2.Value||Checkbox1_3.Value)&& 
              (Checkbox1_5.Value||Checkbox1_6.Value),
//choose checkboxes for 'Client Type'  and 'Staff or Ambassador'
              Filter(test831,If(Checkbox1.Value,'Client Type'="CHD")||If(Checkbox1_1.Value,'Client Type'="HAD")||If(Checkbox1_2.Value,'Client Type'="FD")||If(Checkbox1_3.Value,'Client Type'="AAD"),
If(Checkbox1_4.Value,Successor="yes",Successor="no"),
If(Checkbox1_5.Value,'Staff or Ambassador'="Staff")||If(Checkbox1_6.Value,'Staff or Ambassador'="Ambassador")
                     )
//filter based on 'Client Type' ,'Staff or Ambassador', Successor
              ),
  State)

insert checkbox named Checkbox2 in this gallery

3)gallery2 for filtered 'Waitlisted in Months' column (based on checkboxes) with no-repeated value

set gallery2's Items:

Distinct(
         If(
           !Checkbox1.Value&&!Checkbox1_1.Value&&!Checkbox1_2.Value&&!Checkbox1_3.Value&&
           !Checkbox1_5.Value&&!Checkbox1_6.Value,
//not choose checkboxes for 'Client Type'  and 'Staff or Ambassador'
           Filter(test831,If(Checkbox1_4.Value,Successor="yes",Successor="no")),
//filter on based on Successor
            !Checkbox1.Value&&!Checkbox1_1.Value&&!Checkbox1_2.Value&&!Checkbox1_3.Value&& 
            (Checkbox1_5.Value||Checkbox1_6.Value),
//not choose checkboxes for 'Client Type', choose check box for 'Staff or Ambassador'
            Filter(test831,If(Checkbox1_4.Value,Successor="yes",Successor="no"),
                           If(Checkbox1_5.Value,'Staff or Ambassador'="Staff")||If(Checkbox1_6.Value,'Staff or Ambassador'="Ambassador")),
//filter based on 'Staff or Ambassador' and Successor
             (Checkbox1.Value||Checkbox1_1.Value||Checkbox1_2.Value||Checkbox1_3.Value)
                &&!Checkbox1_5.Value&&!Checkbox1_6.Value,
//choose checkboxes for 'Client Type', not choose check box for 'Staff or Ambassador'
              Filter(test831,If(Checkbox1.Value,'Client Type'="CHD")||If(Checkbox1_1.Value,'Client Type'="HAD")||If(Checkbox1_2.Value,'Client Type'="FD")||If(Checkbox1_3.Value,'Client Type'="AAD"),
If(Checkbox1_4.Value,Successor="yes",Successor="no")),
filter based on 'Client Type' and Successor
              (Checkbox1.Value||Checkbox1_1.Value||Checkbox1_2.Value||Checkbox1_3.Value)&& 
              (Checkbox1_5.Value||Checkbox1_6.Value),
//choose checkboxes for 'Client Type'  and 'Staff or Ambassador'
              Filter(test831,If(Checkbox1.Value,'Client Type'="CHD")||If(Checkbox1_1.Value,'Client Type'="HAD")||If(Checkbox1_2.Value,'Client Type'="FD")||If(Checkbox1_3.Value,'Client Type'="AAD"),
If(Checkbox1_4.Value,Successor="yes",Successor="no"),
If(Checkbox1_5.Value,'Staff or Ambassador'="Staff")||If(Checkbox1_6.Value,'Staff or Ambassador'="Ambassador")
                     )
//filter based on 'Client Type' ,'Staff or Ambassador', Successor
              ),
  'Waitlisted in Months')

insert checkbox named Checkbox3 in this gallery

4)gallery3 to display filter items:

Filter(
If(!Checkbox2.Value&&!Checkbox3.Value,
If(!Checkbox1.Value&&!Checkbox1_1.Value&&!Checkbox1_2.Value&&!Checkbox1_3.Value&&!Checkbox1_5.Value&&!Checkbox1_6.Value,Filter(test831,If(Checkbox1_4.Value,Successor="yes",Successor="no")),!Checkbox1.Value&&!Checkbox1_1.Value&&!Checkbox1_2.Value&&!Checkbox1_3.Value&&(Checkbox1_5.Value||Checkbox1_6.Value),Filter(test831,If(Checkbox1_4.Value,Successor="yes",Successor="no"),If(Checkbox1_5.Value,'Staff or Ambassador'="Staff")||If(Checkbox1_6.Value,'Staff or Ambassador'="Ambassador")),(Checkbox1.Value||Checkbox1_1.Value||Checkbox1_2.Value||Checkbox1_3.Value)&&!Checkbox1_5.Value&&!Checkbox1_6.Value,Filter(test831,If(Checkbox1.Value,'Client Type'="CHD")||If(Checkbox1_1.Value,'Client Type'="HAD")||If(Checkbox1_2.Value,'Client Type'="FD")||If(Checkbox1_3.Value,'Client Type'="AAD"),If(Checkbox1_4.Value,Successor="yes",Successor="no")),(Checkbox1.Value||Checkbox1_1.Value||Checkbox1_2.Value||Checkbox1_3.Value)&&(Checkbox1_5.Value||Checkbox1_6.Value),Filter(test831,If(Checkbox1.Value,'Client Type'="CHD")||If(Checkbox1_1.Value,'Client Type'="HAD")||If(Checkbox1_2.Value,'Client Type'="FD")||If(Checkbox1_3.Value,'Client Type'="AAD"),If(Checkbox1_4.Value,Successor="yes",Successor="no"),If(Checkbox1_5.Value,'Staff or Ambassador'="Staff")||If(Checkbox1_6.Value,'Staff or Ambassador'="Ambassador"))),
//not choose gallery1 and gallery2
Checkbox2.Value&&!Checkbox3.Value,
Filter(
If(!Checkbox1.Value&&!Checkbox1_1.Value&&!Checkbox1_2.Value&&!Checkbox1_3.Value&&!Checkbox1_5.Value&&!Checkbox1_6.Value,Filter(test831,If(Checkbox1_4.Value,Successor="yes",Successor="no")),!Checkbox1.Value&&!Checkbox1_1.Value&&!Checkbox1_2.Value&&!Checkbox1_3.Value&&(Checkbox1_5.Value||Checkbox1_6.Value),Filter(test831,If(Checkbox1_4.Value,Successor="yes",Successor="no"),If(Checkbox1_5.Value,'Staff or Ambassador'="Staff")||If(Checkbox1_6.Value,'Staff or Ambassador'="Ambassador")),(Checkbox1.Value||Checkbox1_1.Value||Checkbox1_2.Value||Checkbox1_3.Value)&&!Checkbox1_5.Value&&!Checkbox1_6.Value,Filter(test831,If(Checkbox1.Value,'Client Type'="CHD")||If(Checkbox1_1.Value,'Client Type'="HAD")||If(Checkbox1_2.Value,'Client Type'="FD")||If(Checkbox1_3.Value,'Client Type'="AAD"),If(Checkbox1_4.Value,Successor="yes",Successor="no")),(Checkbox1.Value||Checkbox1_1.Value||Checkbox1_2.Value||Checkbox1_3.Value)&&(Checkbox1_5.Value||Checkbox1_6.Value),Filter(test831,If(Checkbox1.Value,'Client Type'="CHD")||If(Checkbox1_1.Value,'Client Type'="HAD")||If(Checkbox1_2.Value,'Client Type'="FD")||If(Checkbox1_3.Value,'Client Type'="AAD"),If(Checkbox1_4.Value,Successor="yes",Successor="no"),If(Checkbox1_5.Value,'Staff or Ambassador'="Staff")||If(Checkbox1_6.Value,'Staff or Ambassador'="Ambassador"))),
State in Filter(Gallery1.AllItems,Checkbox2.Value).Result),
//add a filter rule for State
!Checkbox2.Value&&Checkbox3.Value,
Filter(
If(!Checkbox1.Value&&!Checkbox1_1.Value&&!Checkbox1_2.Value&&!Checkbox1_3.Value&&!Checkbox1_5.Value&&!Checkbox1_6.Value,Filter(test831,If(Checkbox1_4.Value,Successor="yes",Successor="no")),!Checkbox1.Value&&!Checkbox1_1.Value&&!Checkbox1_2.Value&&!Checkbox1_3.Value&&(Checkbox1_5.Value||Checkbox1_6.Value),Filter(test831,If(Checkbox1_4.Value,Successor="yes",Successor="no"),If(Checkbox1_5.Value,'Staff or Ambassador'="Staff")||If(Checkbox1_6.Value,'Staff or Ambassador'="Ambassador")),(Checkbox1.Value||Checkbox1_1.Value||Checkbox1_2.Value||Checkbox1_3.Value)&&!Checkbox1_5.Value&&!Checkbox1_6.Value,Filter(test831,If(Checkbox1.Value,'Client Type'="CHD")||If(Checkbox1_1.Value,'Client Type'="HAD")||If(Checkbox1_2.Value,'Client Type'="FD")||If(Checkbox1_3.Value,'Client Type'="AAD"),If(Checkbox1_4.Value,Successor="yes",Successor="no")),(Checkbox1.Value||Checkbox1_1.Value||Checkbox1_2.Value||Checkbox1_3.Value)&&(Checkbox1_5.Value||Checkbox1_6.Value),Filter(test831,If(Checkbox1.Value,'Client Type'="CHD")||If(Checkbox1_1.Value,'Client Type'="HAD")||If(Checkbox1_2.Value,'Client Type'="FD")||If(Checkbox1_3.Value,'Client Type'="AAD"),If(Checkbox1_4.Value,Successor="yes",Successor="no"),If(Checkbox1_5.Value,'Staff or Ambassador'="Staff")||If(Checkbox1_6.Value,'Staff or Ambassador'="Ambassador"))),
'Waitlisted in Months' in Filter(Gallery2.AllItems,Checkbox3.Value).Result),
//add a filter rule for 'Waitlisted in Months'
Checkbox2.Value&&Checkbox3.Value,
Filter(
If(!Checkbox1.Value&&!Checkbox1_1.Value&&!Checkbox1_2.Value&&!Checkbox1_3.Value&&!Checkbox1_5.Value&&!Checkbox1_6.Value,Filter(test831,If(Checkbox1_4.Value,Successor="yes",Successor="no")),!Checkbox1.Value&&!Checkbox1_1.Value&&!Checkbox1_2.Value&&!Checkbox1_3.Value&&(Checkbox1_5.Value||Checkbox1_6.Value),Filter(test831,If(Checkbox1_4.Value,Successor="yes",Successor="no"),If(Checkbox1_5.Value,'Staff or Ambassador'="Staff")||If(Checkbox1_6.Value,'Staff or Ambassador'="Ambassador")),(Checkbox1.Value||Checkbox1_1.Value||Checkbox1_2.Value||Checkbox1_3.Value)&&!Checkbox1_5.Value&&!Checkbox1_6.Value,Filter(test831,If(Checkbox1.Value,'Client Type'="CHD")||If(Checkbox1_1.Value,'Client Type'="HAD")||If(Checkbox1_2.Value,'Client Type'="FD")||If(Checkbox1_3.Value,'Client Type'="AAD"),If(Checkbox1_4.Value,Successor="yes",Successor="no")),(Checkbox1.Value||Checkbox1_1.Value||Checkbox1_2.Value||Checkbox1_3.Value)&&(Checkbox1_5.Value||Checkbox1_6.Value),Filter(test831,If(Checkbox1.Value,'Client Type'="CHD")||If(Checkbox1_1.Value,'Client Type'="HAD")||If(Checkbox1_2.Value,'Client Type'="FD")||If(Checkbox1_3.Value,'Client Type'="AAD"),If(Checkbox1_4.Value,Successor="yes",Successor="no"),If(Checkbox1_5.Value,'Staff or Ambassador'="Staff")||If(Checkbox1_6.Value,'Staff or Ambassador'="Ambassador"))),
State in Filter(Gallery1.AllItems,Checkbox2.Value).Result
'Waitlisted in Months' in Filter(Gallery2.AllItems,Checkbox3.Value).Result)
//add two filter rules for State and 'Waitlisted in Months'
),
If(IsBlank(TextInput1.Text),true,
'FirstName' in TextInput1.Text||
'LastName' in TextInput1.Text||
'Client Number' in TextInput1.Text||
'Zip' in TextInput1.Text||
'email' in TextInput1.Text||
'Phone' in TextInput1.Text
)
)

 Then you will filter based on 7 checkboxes and 2 galleries.

Actually, using checkboxes to filter based on so many fields will be very complex. You need to justify whether it is checked one by one.

If you use combo boxes , issue will be much simpler.

Best regards,

[teaful]

Phoebe Liu,

Wow, this is complex. Thank you, I will work on understanding this and implementing.

I'm not familiar with combo boxes yet. How would you envision them working in my situation? A combo box for each of the column choices?