cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frankie70
Post Patron
Post Patron

Filter collection by dropdown

Hi community,

I've a collection collected with values from 2 SharePoint lists.

I would like to create a dropdown over each column (distinct values) in the gallery that displays the collection.

I am failing though to filter the collection based on a dropdown. When I filter, the dropdown contains only the before selected value.

How can i achieve what I am planning to do? I am a bit stuck here...

Can anybody help me out?

3 ACCEPTED SOLUTIONS

Accepted Solutions
StalinPonnusamy
Super User
Super User

Hi @Frankie70 

 

Gussing your scenario. In the below example, filtering the gallery using the Status/choice column

Items property of Gallery will be 

 

Filter(
    CollectionName,
    (IsBlank(DropdownName.Selected.Value) || DropdownName.Selected.Value = ChoiceColumnName.Value)
)

 

 

Please post your query and dropdown items, so that it helps to troubleshoot.

View solution in original post

StalinPonnusamy
Super User
Super User

Hi @Frankie70 

 

In your example, Collection has many columns and you added one more column called "Result".

 

Using AddColumns you added more columns to Collection.

 

This command uses isblank() equivalent to All IsBlank(DropdownName.Selected.Value) ||.

  • This tells if the dropdown does not have anything then treat it as All.
  • make sure to set the AllowEmptySelection to true to the dropdown.

View solution in original post

StalinPonnusamy
Super User
Super User

Hi @Frankie70 

 

  • Use SortByColumns to sort the list
SortByColumns(
    Filter(
        alldata,
        (IsBlank(Dropdown1.Selected.Result) || Dropdown1.Selected.Result = PStatus.Value)
&& (Other Conditions)
    ),
    "Title",
    If(
        SortByOrder,
        Descending,
        Ascending
    )
)

 

View solution in original post

11 REPLIES 11
StalinPonnusamy
Super User
Super User

Hi @Frankie70 

 

Gussing your scenario. In the below example, filtering the gallery using the Status/choice column

Items property of Gallery will be 

 

Filter(
    CollectionName,
    (IsBlank(DropdownName.Selected.Value) || DropdownName.Selected.Value = ChoiceColumnName.Value)
)

 

 

Please post your query and dropdown items, so that it helps to troubleshoot.

Hi @StalinPonnusamy 

this did work.

I have another question regarding this.

I have build a collection like that and wanted to add "all" to the dropdown. Am I doing this the right way?

ClearCollect(alldata, {Result: "All"});
Collect(
    alldata,
    AddColumns(
        Projects,
        "URLDocLib",
        Documents,
        "Type",
        'Type of Project',
        "PStatus",
        'Project Lifecycle',
        "PNumber",
        'Project-ID'
    )
);
Collect(
    alldata,
    AddColumns(
        Projects_1,
        "URLDocLib",
        Documents,
        "Type",
        TypeofProject,
        "PStatus",
        Status,
        "PNumber",
        Project_x002d_ID
    )
)

Somehow it does not work for me?

StalinPonnusamy
Super User
Super User

Hi @Frankie70 

 

In your example, Collection has many columns and you added one more column called "Result".

 

Using AddColumns you added more columns to Collection.

 

This command uses isblank() equivalent to All IsBlank(DropdownName.Selected.Value) ||.

  • This tells if the dropdown does not have anything then treat it as All.
  • make sure to set the AllowEmptySelection to true to the dropdown.

Hi @StalinPonnusamy ,

I would like to replace the dropdown by a combobox where you can select multiple values and search the gallery.

Filter(
        alldata,
        (IsBlank(ComboBox1.Selected.Result) || ComboBox1.SelectedItems.Result = PStatus.Value)
    )

This does not work because of incompatible types of comparison (table, text).

How can I get this to work?

StalinPonnusamy
Super User
Super User

Hi @Frankie70 

Since Combo box is multi-select. Try this 

With (
    {_item: alldata},
    Filter(
        _item,
        (IsBlank(ComboBox1.SelectedItems) || PStatus.Value in ComboBox1.SelectedItems.Result)
    )
)

 

Note: ComboBox1.SelectedItems.Result or ComboBox1.SelectedItems.ColumnName

Hi @StalinPonnusamy ,

 

thanks, how do include this though in here?

If(IsBlank(inp_search),
SortByColumns(
    Filter(
        alldata,
        (IsBlank(Dropdown1.Selected.Result) || Dropdown1.Selected.Result = PStatus.Value)
    ),
    "Title",
    If(
        SortByOrder,
        Descending,
        Ascending
    )
),
Search(alldata, inp_search.Text, "Title"))

Frankie70_0-1631191305544.png

 

StalinPonnusamy
Super User
Super User

With (
    {_item: alldata},
    If(
        IsBlank(inp_search),
        Sort(
            Filter(
                _item,
                (IsBlank(ComboBox1.SelectedItems) || PStatus.Value in ComboBox1.SelectedItems.Result)
            ),
            "Title",
            If(
                SortByOrder,
                Descending,
                Ascending
            )
        ),
        Search(
            _item,
            inp_search,
            "Title"
        )
    )
)

 

Looks you are filtering either one of them. Is it your requirement? We can optimize your filter further to filter both if needed

Hi @StalinPonnusamy 

I tried your code but it does not fully work.

  • at the beginning, all items should be shown sorted ascending by Title
  • it shall be possible to use the multi select combobox to filter the Status column (there might be more comboboxes ot come to filter other columns)
  • it shall be possible to use full text seach in the status column

It would be great if you could support me.

StalinPonnusamy
Super User
Super User

Hi @Frankie70 

 

This will display all data be default and filters it if any (based on text and combo)

 

With (
    {_item: alldata},
    Sort(
        Filter(
            _item,
            (IsBlank(ComboBox1.SelectedItems) || PStatus.Value in ComboBox1.SelectedItems.Result) || (IsBlank(inp_search.Text) || StartsWith(
                Title,
                inp_search.Text
            ))
        ),
        "Title",
        If(
            SortByOrder,
            Descending,
            Ascending
        )
    )
)

 

 

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (3,953)