cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AKB2021
Helper I
Helper I

Filtering flexible gallery using Text box and combobox

Hello All,

 

To avoid 'in' delegation issue, I have created a Flexible height gallery which is getting filtered based on 2 dropdown values created in app as filter.

1st dropdown value is Status of request, Second dropdown is Group. The backend data is SharePoint list, and both "Status" and "Group" are single selection choice fields.

 

Now after filtering and getting this data, I am hiding and showing rows based on Text input field and a Combobox field. Text input field searches the "description" columns in SharePoint which are "TitleDetails" of the request and "DetailedDescription" of the request. I also have a combobox column in SharePoint called "Country" which I want to use to filter along with the textbox field. The code for gallery works fine 

 

 

 

If(
    drpdwnStatus.Selected.Value = "All",
    If(
        drpGroupSelection.Selected.Name = "All",
        TotalList,
        Filter(
            TotalList,
            GroupName = drpGroupSelection.Selected.Name
        )
    ),
    If(
        drpGroupSelection.Selected.Name = "All",
        Filter(
            TotalList,
            Status.Value = drpdwnStatus.Selected.Value
        ),
        Filter(
            TotalList,
            GroupName = drpGroupSelection.Selected.Name,
            Status.Value = drpdwnStatus.Selected.Value
        )
    )
)

 

 

 

There are multiple labels created in this gallery, which I have grouped together and then for the entire group, visible property is added as 

 

 

If(
    IsBlank(txtSearchBox),
    true,
    txtSearchBox.Text in ThisItem.'TitleDetails' || TextSearchBox_1.Text in ThisItem.DetailedDescription 
)

 

 

Till this the code works fine. The moment I add the code as below, the gallery goes in error.

 

 

If(
    IsBlank(txtSearchBox),
    true,
    txtSearchBox.Text in ThisItem.'TitleDetails' || txtSearchBox.Text in ThisItem.DetailedDescription && Concat(comboBoxCountry.SelectedItems,Value&",") in Concat(ThisItem.Country,Value&",")
)

 

 

 

Can someone please help me how to add the combobox piece to the code for hide/show of gallery rows?

 

Thanks,

AK

2 ACCEPTED SOLUTIONS

Accepted Solutions
StalinPonnusamy
Super User
Super User

 

Updated based on your field names

 

 

With({galData: Filter(TotalList, IsBlank(comboBoxCountry.SelectedItems) || IsEmpty(comboBoxCountry.SelectedItems) || CountryDetails.Value = comboBoxCountry.SelectedItems.Value, drpGroupSelection.Selected.Value = "All" || ApplicationName = drpGroupSelection.SelectedText.Value)},If(Not(IsBlank(txtSearchBox.Text)),Search(galData,txtSearchBox.Text,"TitleDetails","DetailedDescription "),galData))

 

 

Please verify drpGroupSelection.SelectedText.Value & drpGroupSelection.Selected.Value which one has "All"

 

Example

 

With(
    {
        sData: Filter(
            'Issue tracker',
            IsBlank(ComboBox3.SelectedItems.Value) || ComboBox3.Selected.Value ="All" || IsEmpty(ComboBox3.SelectedItems) || Status.Value = ComboBox3.Selected.Value,
            IsBlank(Dropdown3.Selected.Value) || Dropdown3.SelectedText.Value ="All" || Dropdown3.SelectedText.Value="Critical"
        )
    },
    If(
        Not(IsBlank(TextInput2.Text)),
        Search(
            sData,
            TextInput2.Text,
            "Title","Description"
        ),
        sData
    )
)

View solution in original post

So I solved it by converting the multi select column values in a Text column in SharePoint. Then I just used a search command with a single value dropdown in powerapps instead of multi select combobox and everything worked.

 

With(
    {
        galData: Filter(
            TotalList,
            IsBlank(comboBoxCountry.SelectedItems) || IsEmpty(comboBoxCountry.SelectedItems),
            drpGroupSelection.Selected.Value  = "All" || ApplicationName = drpGroupSelection.Selected.Value 
        )
    },
    If(
        Not(IsBlank(txtSearchBox.Text)),
        Search(
            galData,
            txtSearchBox.Text,
            "TitleDetails",
	    "DetailedDescription "
        ),
        Not(drpCountry.Selected.Value = "All"),
        Search(
            galData,
            drpCountry.Selected.Value,
            "countryTextValues"
        ),
        galData
    )
)

View solution in original post

11 REPLIES 11
StalinPonnusamy
Super User
Super User

In the below example filters using 1 dropdown, 1 date and 1 text search. All are optional

 

 

 

With(
    {
        sData: Filter(
            'Issue tracker',
            IsBlank(Dropdown3.Selected.Value) || IsEmpty(drpdwnStatus.Selected.Value) || Status.Value = drpdwnStatus.Selected.Value,
 IsBlank(drpGroupSelection.SelectedItems.Value) || IsEmpty(drpGroupSelection.Selected.Value) || Status.Value = drpGroupSelection.Selected.Name
        )
    },
    If(
        Not(IsBlank(txtSearchBox.Text)),
        Search(
            sData,
            txtSearchBox.Text,
            "TitleDetails","DetailedDescription"
        ),
        sData
    )
)

 

 

Edit: Updated with your field(s)

 

Sample:

StalinPonnusamy_0-1627525563194.png

 

 

 

StalinPonnusamy
Super User
Super User

not sure you are using dropdown or combo. Just to show the different usage for dropdown and combo

 

    {
        sData: Filter(
            'Issue tracker',
            IsBlank(ComboBox3.SelectedItems.Value) || IsEmpty(ComboBox3.SelectedItems) || Priority.Value = ComboBox3.Selected.Value,
            IsBlank(Dropdown3.Selected.Value) || IsEmpty(Dropdown3.Selected.Value) || Priority.Value = Dropdown3.Selected.Value
        )
    },

 

It is combo box as well dropdown. Currently in my setup it is 2 dropdowns, "Status
and "Group" along with 1 combobox "Country" and 1 search box "txtSearchBox". The solution which you have mentioned is more on lines about changing the entire gallery items formula. Let me try it out if this works.

AKB2021
Helper I
Helper I

This solution gives me error as "Incompatible types for comparison. These types can't be compared. Table, Table" for this piece CountryDetails.Value = comboBoxCountry.SelectedItems.Name

 

With({galData: Filter(TotalList, IsBlank(comboBoxCountry.SelectedItems.Name) || IsEmpty(comboBoxCountry.SelectedItems) || CountryDetails.Value = comboBoxCountry.SelectedItems.Name, drpGroupSelection.Selected.Name = "All" || ApplicationName = drpGroupSelection.Selected.Name)},If(Not(IsBlank(txtSearchBox.Text)),Search(galData,txtSearchBox.Text,"TitleDetails","DetailedDescription "),galData))

 

Will have to check if this later gives me delegation error or not as my dataset will go over 500 records and reach over 2000 by end of this year.

StalinPonnusamy
Super User
Super User

Please update to

 

 

 

comboBoxCountry.Selected.Value
drpGroupSelection.Selected.Value

 

 

Still 

drpGroupSelection.Selected.Value = "All"
StalinPonnusamy
Super User
Super User

 

Updated based on your field names

 

 

With({galData: Filter(TotalList, IsBlank(comboBoxCountry.SelectedItems) || IsEmpty(comboBoxCountry.SelectedItems) || CountryDetails.Value = comboBoxCountry.SelectedItems.Value, drpGroupSelection.Selected.Value = "All" || ApplicationName = drpGroupSelection.SelectedText.Value)},If(Not(IsBlank(txtSearchBox.Text)),Search(galData,txtSearchBox.Text,"TitleDetails","DetailedDescription "),galData))

 

 

Please verify drpGroupSelection.SelectedText.Value & drpGroupSelection.Selected.Value which one has "All"

 

Example

 

With(
    {
        sData: Filter(
            'Issue tracker',
            IsBlank(ComboBox3.SelectedItems.Value) || ComboBox3.Selected.Value ="All" || IsEmpty(ComboBox3.SelectedItems) || Status.Value = ComboBox3.Selected.Value,
            IsBlank(Dropdown3.Selected.Value) || Dropdown3.SelectedText.Value ="All" || Dropdown3.SelectedText.Value="Critical"
        )
    },
    If(
        Not(IsBlank(TextInput2.Text)),
        Search(
            sData,
            TextInput2.Text,
            "Title","Description"
        ),
        sData
    )
)

Thanks for lot for responding so quickly. Really appreciate you spending your time on this! I am still getting the error for Table, Table comparision. If I remove that section, code works great which means combo box filter is not available for the gallery. Here is the updated code I am using which works great.

 

With(
    {
        galData: Filter(
            TotalList,
            IsBlank(comboBoxCountry.SelectedItems) || IsEmpty(comboBoxCountry.SelectedItems),
            drpGroupSelection.Selected.Value  = "All" || ApplicationName = drpGroupSelection.Selected.Value 
        )
    },
    If(
        Not(IsBlank(txtSearchBox.Text)),
        Search(
            galData,
            txtSearchBox.Text,
            "TitleDetails",
	    "DetailedDescription "
        ),
        galData
    )
)

 

 

The moment I add this, the code breaks 

 

|| CountryDetails.Value = comboBoxCountry.SelectedItems.Value

 

Delegation warning. "Incompatible types for comparison. These types can't be compared. Table, Table"

 

Here CountryDetails is Multiselect checkbox column in SharePoint

AKB2021_0-1627591508969.png

and comboBoxCountry is Combobox in PowerApps with multi selection On.

 

So I solved it by converting the multi select column values in a Text column in SharePoint. Then I just used a search command with a single value dropdown in powerapps instead of multi select combobox and everything worked.

 

With(
    {
        galData: Filter(
            TotalList,
            IsBlank(comboBoxCountry.SelectedItems) || IsEmpty(comboBoxCountry.SelectedItems),
            drpGroupSelection.Selected.Value  = "All" || ApplicationName = drpGroupSelection.Selected.Value 
        )
    },
    If(
        Not(IsBlank(txtSearchBox.Text)),
        Search(
            galData,
            txtSearchBox.Text,
            "TitleDetails",
	    "DetailedDescription "
        ),
        Not(drpCountry.Selected.Value = "All"),
        Search(
            galData,
            drpCountry.Selected.Value,
            "countryTextValues"
        ),
        galData
    )
)
StalinPonnusamy
Super User
Super User

Hi @AKB2021 

 

I'm glad it worked. Thanks for the confirmation. We appreciate it.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,053)