Multi filter on gallery

[BaptisteP]

Hello,

Considering the following tables:

I would like to reproduce the following scheme, i.e. the display of a gallery according to a search bar and several dropdown selectors.

The filters can be selected or not, the search bar filled or not.

At the moment I have not managed to integrate the filters and I only have this formula for the items in my gallery:

SortByColumns(
    Search(
        Employee,
        InputSearch.Text,
        "Name",
        "Firstname"
    );
    "ID",
    Descending
)

How can I integrate the different dropdowns as filter in this query?

I've already tried several solutions, like adding the addcolumn or filter function in the query, but I get stuck every time.

Thank you,

Baptiste

[One]

hi @BaptisteP,

you are going to need an if statement in the Items property of your gallery to check if each dropdown/search box are empty or not, then have it filter by the value that's in whichever control has data. This formula uses 2 dropdowns and a Txt search box and will filter by all the ones that have data, or display the whole list if they are empty. you can adjust the following code with your list name/ dropdown names/ search txt box name in the Items property of your gallery:

If(

!IsBlank(SearchText) && IsBlank(DropDown1) && IsBlank(DropDown2),

Filter(ListName, StartsWith(SearchColumnName,SearchText.Text)),

IsBlank(SearchText) && !IsBlank(DropDown1) && IsBlank(DropDown2),

Filter(ListName, DropDown1.Selected.Value = DropDown1ColumnName),

IsBlank(SearchText) && IsBlank(DropDown1) && !IsBlank(DropDown2),

Filter(ListName, DropDown2.Selected.Value = DropDown2ColumnName),

IsBlank(SearchText) && !IsBlank(DropDown1) && !IsBlank(DropDown2),

Filter(ListName, DropDown1.Selected.Value = DropDown1ColumnName && DropDown2.Selected.Value = DropDown2ColumnName),

!IsBlank(SearchText) && !IsBlank(DropDown1) && IsBlank(DropDown2),

Filter(ListName, DropDown1.Selected.Value = DropDown1ColumnName && StartsWith(SearchColumnName,SearchText.Text)),

!IsBlank(SearchText) && IsBlank(DropDown1) && !IsBlank(DropDown2),

Filter(ListName, DropDown2.Selected.Value = DropDown2ColumnName && StartsWith(SearchColumnName,SearchText.Text)),

!IsBlank(SearchText) && !IsBlank(DropDown1) && !IsBlank(DropDown2),

Filter(ListName, DropDown1.Selected.Value = DropDown1ColumnName && DropDown2.Selected.Value = DropDown2ColumnName && StartsWith(SearchColumnName,SearchText.Text)),

ListName)

------------------------------------------------------------------------------------------------------------------------------
Please click "Accept as Solution" if this post answered your question so that it is helpful for people having similar questions. If you found this post helpful smack that Thumbs up button! Thanks!

[Feiteira]

Hi there @BaptisteP,

I just came up with an idea that actualy works.

Having all of the dropdowns or even text inputs you might need, try to do something like this one:

Filter(
    'Nome da Lista',

    If( !IsBlank(TextInput1.Text), TextInput1.Text in Assunto, true )
    
    And
    
    If(
        !IsBlank(ComboBox1.Selected.Value),
        ComboBox1.Selected.Value in 'Choice Field'.Value,
        true
    )
)

It's basically just checking if any field has some value, and if it has value, filters the list acording to any of those values.

Let me know if this worked for you and don't forget to click on the "Accept as Solution" 😉

[BaptisteP]

Thanks for your answers !

@One I've tried something like this, but this solution seems a bit overkill, in my real application I have 6 filters for the moment (which can and will evolve) which makes 64 lines to write at minimum to test all the combinations, and which doubles with each addition of filter, it is thus not a very maintainable solution, not to mention the debugging.

@Feiteira I tried this too, but I don't seem to be doing it right because the filter doesn't work in any way, and nothing is displayed until I have at least one value (even zero/null) in each of the filters and in the search bar.

Without wanting to take up too much of your time, could you show me a mini concrete application of this function?

[One · Accepted solution]

@BaptisteP, 

it absolutely is overkill, this might help you better, you can adjust the following:

Filter( 'ListName', Dropdown1.Selected.Value=Blank() Or Column1Name=Dropdown1.Selected.Value, Dropdown2.Selected.Value=Blank() Or Column2Name=Dropdown2.Selected.Value, Dropdown3.Selected.Value=Blank() Or Column3Name=Dropdown3.Selected.Value )

 for further details please see the related article here

------------------------------------------------------------------------------------------------------------------------------
Please click "Accept as Solution" if this post answered your question so that it is helpful for people having similar questions. If you found this post helpful smack that Thumbs up button! Thanks!

@BaptisteP

[Feiteira]

Do you mind sharing your code? maybe a fresh pair of eyes can help 😋
Have a look at my code, straight from the PowerApp, let me know if it helps.

Don't mind the collumn names, as I did it to test that code.

that button was just to help me create record into the List (I did it using SharePoint Lists but it would work the same with Dataverse or any other source)

Let me know if it helps and works for you 😀

[BaptisteP]

The solution in this article works perfectly, thanks !

[pablik01]

Hi everyone, I have a problem with double filtering. Currently the code looks like this:

With({asmt:Filter(
                    'TELEFONY — kopia';StartsWith(
                                          Nazwisko;szukaj_box.Text
                                )
            )
       };
    If(
       filtr.Selected.Result = "Pokaż wszystkich pracowników"; asmt;
                                Filter(
                                       asmt;Stanowisko=filtr.Selected.Result
                                 )
                                 
  )
)

I would like to add another filter called floor_number. This is my next approach to the topic, I tried it but I keep getting an error