Multiple combo box & Search Bar for filtering gallery

[Stephenp88]

Hello!

I'm in the process of creating an application for users to view items in a SharePoint list (without needing to access the list itself - for work related reasons).

I have two Combo Boxes, one that filters by Department Area (A choice column in SharePoint that allows you to select more than one option) and the other that filters by status (Single choice column in SharePoint that allows you to select from the following: Open, Closed, Pending, Approved, Denied)

Additionally, I have a Search Box that filters the 'Title' column in the SharePoint list (which is used for the items reference number *which can also be TBC or N/A*)

A genius ( @Amik ) produced the below formula which works however; it requires you to select an option in the two dropdown boxes before the items are displayed in the gallery:

If(
    CountRows(YourComboBox.SelectedItems) = 0,
    Search(
        Filter(
            'Your Data',
            CountRows(YourStatusCombo.SelectedItems) = 0 || Status.Value in YourStatusCombo.SelectedItems.Value
        ),
        TextSearchBox1.Text,
        "Title"
    ),
    Search(
        Filter(
            Ungroup(
                ForAll(
                    YourComboBox.SelectedItems As _selections,
                    Filter(
                        'Your Data',
                        _selections.Value in Branchlead.Value
                    )
                ),
                "Value"
            ),
            CountRows(YourStatusCombo.SelectedItems) = 0 || Status.Value in YourStatusCombo.SelectedItems.Value
        ),
        TextSearchBox1.Text,
        "Title"
    )
)

He tried to remedy the issue by producing the below formula which unfortunately produced errors ('ThisItem' isn't recognised) on all the gallery boxes (sorry, unsure of the correct terminology, but essentially, all of the things that make up the gallery, such as the text that displays the title of the item etc):

If(
    CountRows(YourComboBox.SelectedItems) = 0 || IsBlank(YourComboBox.Selected),
    Search(
        Filter(
            'Your Data',
            CountRows(YourStatusCombo.SelectedItems) = 0 || IsBlank(YourStatusCombo.SelectedItems) || Status.Value in YourStatusCombo.SelectedItems.Value
        ),
        TextSearchBox1.Text,
        "Title"
    ),
    Search(
        Filter(
            Ungroup(
                ForAll(
                    YourComboBox.SelectedItems As _selections,
                    Filter(
                        'Your Data',
                        _selections.Value in Branchlead.Value
                    )
                ),
                "Value"
            ),
            CountRows(YourStatusCombo.SelectedItems) = 0 || Status.Value in YourStatusCombo.SelectedItems.Value
        ),
        TextSearchBox1.Text,
        "Title"
    )
)

He unfortunately couldn't reproduce the error and recommended I post this problem in hopes that another community member might find a solution.

Your help will be greatly appreciated!

Best wishes 

[WarrenBelz · Accepted solution]

Hi @Stephenp88 ,

This is a different approach you might try

With(
   {
      _Data:
      Search(
         Filter(
            'Your Data',
            CountRows(YourStatusCombo.SelectedItems) = 0 || 
            Status.Value in YourStatusCombo.SelectedItems.Value
         ),
         TextSearchBox1.Text,
         "Title"
      )
   },
   If(
      CountRows(YourComboBox.SelectedItems) = 0,
      _Data,
       Ungroup(
         ForAll(
            YourComboBox.SelectedItems As _selections,
            Filter(
               _Data,
               _selections.Value in Branchlead.Value
            )
         ),
         "Value"
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[Mglat]

try to add IsEmpty. 
Everywhere the condition IsBlank(combobox…) is used, also add    || IsEmpty(combobox..)

my experience is that the combobox behaves different on first enter on page vs select a value and then deselect

[Amik]

@WarrenBelz much cleaner thank you!

[Stephenp88]

Hi @WarrenBelz,

Thank you for the support! 
I’ve used the suggested formula which does work in replacement for the first formula but still doesn’t load the list on initial load prior to selecting options in the two comboboxes- just a blank gallery until you select a status and a branch lead.

best wishes

[WarrenBelz]

@Stephenp88 ,

I tend to use the CountRows which works for me, but you can use

IsEmpty(YourStatusCombo.SelectedItems)

or

Len(YourStatusCombo.Selected.Value) = 0

[Stephenp88]

Sorry @WarrenBelz - I’m still new to PowerApps, can you explain where I would enter that formula? Or are you proposing I replace the CountRows section of the formula you posted above with IsEmpty?

[WarrenBelz]

@Stephenp88 ,

Yes - in both places if your issue is that no items are being displayed until something is selected. You can also use both with || (Or) between them.

IsEmpty(YourStatusCombo.SelectedItems) || Len(YourStatusCombo.Selected.Value) = 0

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[Stephenp88]

@WarrenBelz ,

it accepts the formula (which I think I’ve entered incorrectly - see picture) but it now shows no items and the comboboxes do nothing

[WarrenBelz]

@Stephenp88 ,

You did not post a picture, but please post your exact code in Text.