Filter collection by dropdown

[Frankie70]

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?

[StalinPonnusamy · Accepted solution]

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.

[Frankie70]

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 · Accepted solution]

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.

[Frankie70]

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]

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

[Frankie70]

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"))

[StalinPonnusamy]

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

[Frankie70]

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]

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
        )
    )
)