cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SamPhillips
Level 8

Filtering Gallery with Multiple Checkboxes

Hello!
I have a gallery which I would like to filter with up to 20 checkboxes.

 

Currently I have started the filtering by using the Switch command. But after only coding the gallery to filter for 4 checkboxes i'm realising that the formula is going to be stupidly large and unmanagable with 20+ checkboxes. My current code is below, is there a better way of doing this? As at this rate i will eventually have to code 400 filters, (20 squared). as that is how many possible configurations there is for 20 checkboxes. I think i need to construct the filter depending on what checkboxes are selected, rather than switching between a huge list of filters for each checkbox combination. I hope this makes sense!

 

Switch(
    true,
    Not(Checkbox1.value) && Not(Checkbox2.value) && Not(Checkbox3.value) && Not(Checkbox4.value),
    SortByColumns(
        (Filter(
            MainCustomerList,
            StartsWith(
                NAME,
                TextInput4.Text
            ),
            NAME <> ""
        )),
        "NAME",
        Ascending
    ),
    Checkbox1.value && Not(Checkbox2.value) && Not(Checkbox3.value) && Not(Checkbox4.value),
    SortByColumns(
        (Filter(
            MainCustomerList,
            Field1 = true,
            StartsWith(
                NAME,
                TextInput4.Text
            )
        )),
        "NAME",
        Ascending
    ),
    Not(Checkbox1.value) && Checkbox2.value && Not(Checkbox3.value) && Not(Checkbox4.value),
    SortByColumns(
        (Filter(
            MainCustomerList,
            Field2 = true,
            StartsWith(
                NAME,
                TextInput4.Text
            )
        )),
        "NAME",
        Ascending
    ),
    Checkbox1.value && Checkbox2.value && Not(Checkbox3.value) && Not(Checkbox4.value),
    SortByColumns(
        (Filter(
            MainCustomerList,
            Field1 = true,
            Field2 = true,
            StartsWith(
                NAME,
                TextInput4.Text
            )
        )),
        "NAME",
        Ascending
    ),
    Checkbox1.value && Checkbox2.value && Checkbox3.value && Not(Checkbox4.value),
    SortByColumns(
        (Filter(
            MainCustomerList,
            Field1 = true,
            Field2 = true,
            Field3 = true,
            StartsWith(
                NAME,
                TextInput4.Text
            )
        )),
        "NAME",
        Ascending
    ),
    Not(Checkbox1.value) && Not(Checkbox2.value) && Checkbox3.value && Not(Checkbox4.value),
    SortByColumns(
        (Filter(
            MainCustomerList,
            Field3 = true,
            StartsWith(
                NAME,
                TextInput4.Text
            )
        )),
        "NAME",
        Ascending
    ),
    Not(Checkbox1.value) && Checkbox2.value && Checkbox3.value && Not(Checkbox4.value),
    SortByColumns(
        (Filter(
            MainCustomerList,
            Field2 = true,
            Field3 = true,
            StartsWith(
                NAME,
                TextInput4.Text
            )
        )),
        "NAME",
        Ascending
    ),
    Checkbox1.value && Not(Checkbox2.value) && Checkbox3.value && Not(Checkbox4.value),
    SortByColumns(
        (Filter(
            MainCustomerList,
            Field1 = true,
            Field3 = true,
            StartsWith(
                NAME,
                TextInput4.Text
            )
        )),
        "NAME",
        Ascending
    ),Not(Checkbox1.value) && Not(Checkbox2.value) && Not(Checkbox3.value) && Checkbox4.value,
    SortByColumns(
        (Filter(
            MainCustomerList,
            NAME in Filter(Contracts, "Service" in Service.Value,Contract_x0020_Status = "Active").Customer_x0020_Name,
            
            StartsWith(
                NAME,
                TextInput4.Text
            )
        )),
        "NAME",
        Ascending
    ),Not(Checkbox1.value) && Not(Checkbox2.value) && Checkbox3.value && Checkbox4.value,
    SortByColumns(
        (Filter(
            MainCustomerList,
            NAME in Filter(Contracts, "Service" in Service.Value,Contract_x0020_Status = "Active").Customer_x0020_Name,
            Field3 = true,
            StartsWith(
                NAME,
                TextInput4.Text
            )
        )),
        "NAME",
        Ascending
    ),Not(Checkbox1.value) && Checkbox2.value && Checkbox3.value && Checkbox4.value,
    SortByColumns(
        (Filter(
            MainCustomerList,
            NAME in Filter(Contracts, "Service" in Service.Value,Contract_x0020_Status = "Active").Customer_x0020_Name,
            Field3 = true,Field2 = true,
            StartsWith(
                NAME,
                TextInput4.Text
            )
        )),
        "NAME",
        Ascending
    ),Checkbox1.value && Checkbox2.value && Checkbox3.value && Checkbox4.value,
    SortByColumns(
        (Filter(
            MainCustomerList,
            NAME in Filter(Contracts, "Service" in Service.Value,Contract_x0020_Status = "Active").Customer_x0020_Name,
            Field3 = true,Field2 = true,Field1 = true,
            StartsWith(
                NAME,
                TextInput4.Text
            )
        )),
        "NAME",
        Ascending
    ),Checkbox1.value && Not(Checkbox2.value) && Not(Checkbox3.value) && Checkbox4.value,
    SortByColumns(
        (Filter(
            MainCustomerList,
            NAME in Filter(Contracts, "Service" in Service.Value,Contract_x0020_Status = "Active").Customer_x0020_Name,
            Field1 = true,
            StartsWith(
                NAME,
                TextInput4.Text
            )
        )),
        "NAME",
        Ascending
    ),Not(Checkbox1.value) && Checkbox2.value && Not(Checkbox3.value) && Checkbox4.value,
    SortByColumns(
        (Filter(
            MainCustomerList,
            NAME in Filter(Contracts, "Service" in Service.Value,Contract_x0020_Status = "Active").Customer_x0020_Name,
            Field2 = true,
            StartsWith(
                NAME,
                TextInput4.Text
            )
        )),
        "NAME",
        Ascending
    ),Checkbox1.value && Checkbox2.value && Not(Checkbox3.value) && Checkbox4.value,
    SortByColumns(
        (Filter(
            MainCustomerList,
            NAME in Filter(Contracts, "Service" in Service.Value,Contract_x0020_Status = "Active").Customer_x0020_Name,
            Field2 = true,Field1 = true,
            StartsWith(
                NAME,
                TextInput4.Text
            )
        )),
        "NAME",
        Ascending
    )
)
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Filtering Gallery with Multiple Checkboxes

Hi @SamPhillips,

 

Do you want to construct the filter based on what Checkboxes are selected?

Further, could you please share a bit more about the data type of the Field1, Field2 and etc within your SP list? Are they Yes/No type columns?

 

Currently, there is no simple way to filter Gallery based on Multiple Checkboxes (up to 20 Checkboxs). As an alternative solution, please take a try with the following workaround:

 

I assume that you want to filter your Gallery based on three Checkboxes, I have made a test on my side, please take a try with the following workaround:

5.JPG

 

6.JPG

 

7.JPG

 

 

Set the OnVisible property of the first screen of your app to following formula:

 

ClearCollect(RecordsCollection,'20180820_case9') /* '20180820_case9' represents the SP list within my app*/

 

On your side, you should type the following formula:

ClearCollect(RecordsCollection,MainCustomerList)

Set the OnCheck property of the Checkbox1 to following formula:

ClearCollect(RecordsCollection,Filter(RecordsCollection,Field1=true))

Set the OnCheck property of the Checkbox2 to following formula:

ClearCollect(RecordsCollection,Filter(RecordsCollection,Field2=true))

Set the OnCheck property of the Checkbox3 to following formula:

ClearCollect(RecordsCollection,Filter(RecordsCollection,Field3=true))

Note: The Field1, Field2, Field3 are both Yes/No type columns in my SP list data source.

 

Set the Items property of the Gallery control to following formula:

Filter(RecordsCollection,StartsWith(Title,TextInput1.Text))

Set the OnSelect property of the "Reset Check Option" button control to following formula:

Reset(Checkbox1);Reset(Checkbox2);Reset(Checkbox3);ClearCollect(RecordsCollection,'20180820_case9')

Note: If you want to re-choose Checkbox option, please click the "Reset Check Option"  button.

 

The GIF image as below:Test1.gif

 

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 REPLIES 8
Community Support Team
Community Support Team

Re: Filtering Gallery with Multiple Checkboxes

Hi @SamPhillips,

 

Do you want to construct the filter based on what Checkboxes are selected?

Further, could you please share a bit more about the data type of the Field1, Field2 and etc within your SP list? Are they Yes/No type columns?

 

Currently, there is no simple way to filter Gallery based on Multiple Checkboxes (up to 20 Checkboxs). As an alternative solution, please take a try with the following workaround:

 

I assume that you want to filter your Gallery based on three Checkboxes, I have made a test on my side, please take a try with the following workaround:

5.JPG

 

6.JPG

 

7.JPG

 

 

Set the OnVisible property of the first screen of your app to following formula:

 

ClearCollect(RecordsCollection,'20180820_case9') /* '20180820_case9' represents the SP list within my app*/

 

On your side, you should type the following formula:

ClearCollect(RecordsCollection,MainCustomerList)

Set the OnCheck property of the Checkbox1 to following formula:

ClearCollect(RecordsCollection,Filter(RecordsCollection,Field1=true))

Set the OnCheck property of the Checkbox2 to following formula:

ClearCollect(RecordsCollection,Filter(RecordsCollection,Field2=true))

Set the OnCheck property of the Checkbox3 to following formula:

ClearCollect(RecordsCollection,Filter(RecordsCollection,Field3=true))

Note: The Field1, Field2, Field3 are both Yes/No type columns in my SP list data source.

 

Set the Items property of the Gallery control to following formula:

Filter(RecordsCollection,StartsWith(Title,TextInput1.Text))

Set the OnSelect property of the "Reset Check Option" button control to following formula:

Reset(Checkbox1);Reset(Checkbox2);Reset(Checkbox3);ClearCollect(RecordsCollection,'20180820_case9')

Note: If you want to re-choose Checkbox option, please click the "Reset Check Option"  button.

 

The GIF image as below:Test1.gif

 

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
SamPhillips
Level 8

Re: Filtering Gallery with Multiple Checkboxes

Hi kris, thank you for the code, however (correct me if I'm wrong) this would not cover if multiple checkboxes are selected /checked?
SamPhillips
Level 8

Re: Filtering Gallery with Multiple Checkboxes

And to answer your questions, yes, I want to filter the datasource based on whatever checkboxes are "checked", which could be any combination. The fields I'm filtering by are both yes/no and text (using the 'in' operator), which you can see in my example code. Also below:

Filter(
MainCustomerList,
NAME in Filter(Contracts, "Service" in Service.Value,Contract_x0020_Status = "Active").Customer_x0020_Name,
Field2 = true,Field1 = true,
StartsWith(
NAME,
TextInput4.Text
)
)

Community Support Team
Community Support Team

Re: Filtering Gallery with Multiple Checkboxes

Hi @SamPhillips,

 

Have you taken a try with above solution I provided?

 

Currently, there is no simple way to filter Gallery items based on multiple Checkbox controls within PowerApps currently. As an alternative solution, you could consider take a try with above solution I provided (I think it could solve your problem).

 

Firstly, save your SP list items (your SP list data source) into a Collection within your app. When you check a Checkbox (e.g. Checkbox1) within your app, filter the collection based on corresponding field (e.g. Field1) and update the collection with the filtered results.

 

Set the OnVisible property of the first screen of your app to following formula:

ClearCollect(RecordsCollection,'20180820_case9') /* '20180820_case9' represents the SP list within my app*/

Set the OnCheck property of the Checkbox1 control to following formula:

ClearCollect(RecordsCollection,Filter(RecordsCollection,Field1=true))

If you want to re-choose Checkbox control, please click the "Reset Check Option" button firstly. The OnSelect property of the "Reset Check Option" button control to following formula:

Reset(Checkbox1);
Reset(Checkbox2);
Reset(Checkbox3);
ClearCollect(RecordsCollection,'YourSPList')

Note: UnChecking the Checkbox would not make effect.

 

On your side, you should type the following formula within the Items property of the Gallery control:

Filter(
  RecordsCollection,
  NAME in Filter(Contracts, "Service" in Service.Value,Contract_x0020_Status = "Active").Customer_x0020_Name,
  StartsWith(
           NAME,
           TextInput4.Text
          )
)

Best regards,

Kris

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
SamPhillips
Level 8

Re: Filtering Gallery with Multiple Checkboxes

Hello Kris, 

 

This solution does not fit the requirement as it only filters the datasource by the last checkbox that is selected. 

For example, if I wanted to return only red and blue results in the below list I would check two checkboxes "red" and "blue" (want to filter by both), when using your solution the collection is only filtered by the last checkbox selected "blue"

 

List:

1.Green

2.Red

3.Blue

4.Yellow

5.Red

6.Blue

 

Expected Filtered List:

2.Red

3.Blue

5.Red

6.Blue

 

Result with your solution:

3.Blue

6.Blue

SamPhillips
Level 8

Re: Filtering Gallery with Multiple Checkboxes

Hi Kris, 

 

Apologies, I now have this working following your advise. I had not taken into account that when checking the second checkbox you are further filtering the collection that has already been filtered when checking the first checkbox.

 

I incorrectly assumed that the clearcollect on the second checkbox would clear the collection that was was filtered by the first checkbox and then collect results based only on the second checboxes filter. However, as the clearcollect on the oncheck property is actually utilising the collection before it is cleared, you are able to "stack" filters on top of one another.

 

Thank you for your help on this once again!

 

As a side note, I added further formatting to disable the checkbox once it is checked to not allow "unchecking" which forces the user to use the reset button. 

cooper33
Level 8

Re: Filtering Gallery with Multiple Checkboxes

@v-xida-msft it seems like with this solution you could only have the checkboxes each filter different fields. Is it true that there is still no way to have them control one field?

Example: 3 checkboxes labeled Dog, Cat, Horse. When Dog and Cat are checked, items appear in gallery where Animal field equals Dog or Animal field equals Cat

llacefield
Level: Powered On

Re: Filtering Gallery with Multiple Checkboxes

Also,

Would this solution work for a SharePoint list with over 500 items? Do collections have the capability to hold that many records? and if they do, wouldn't that delgate the processing to the App and not the back end, SharePoint? 

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 190 members 4,514 guests
Recent signins:
Please welcome our newest community members: