Help Needed: Filtering PowerApps Gallery with Multiple ComboBoxes and a Search Field

[zizzer]

Hello everyone,

I'm working on a PowerApps project where I have a gallery (RecordsGallery1_1) displaying items from a SharePoint list (Volunteer Database). I need to filter this gallery based on the inputs from four ComboBoxes and a search input field. The ComboBoxes are for filtering based on multiple choice fields from the SharePoint list: Status, Availability, Schedule, and Interests. Additionally, there's a search input field (SearchInput1_1) for a text-based search on the Full Name column, which is a calculated field in SharePoint.

What Works: I successfully implemented the text search feature. The gallery updates in real-time, showing items that contain the search string within the Full Name field.

plaintextCopy code

If( IsBlank(SearchInput1_1.Text), 'Volunteer Database', // Display all items if the search field is empty Filter( 'Volunteer Database', Lower(SearchInput1_1.Text) in Lower('Full Name') // Perform a case-insensitive search ) )

The Challenge: The issue arises when trying to incorporate the ComboBoxes for the choice fields. Ideally, I want to apply all filters simultaneously so that users can narrow down their search using any combination of the search field and the ComboBoxes. However, I've encountered several error messages when attempting to adjust the gallery's Items property to include the ComboBox selections.

For example, using .Result or .Value with ComboBox selections (e.g., FilterStatus.SelectedItems.Result) often results in error messages like "invalid argument type" or "cannot use Table values in this context". I've tried several variations to include these ComboBox filters alongside the working search field filter, but without success.

Errors Encountered:

• "Invalid argument type (Record). Expecting a Table value instead."
• "The function 'Filter' has some invalid arguments."
• "Cannot use Table values in this context."

  Sample Code for ComboBox Filtering (Not Working):

  plaintextCopy code

  // Example attempt that results in errors Filter( 'Volunteer Database', (IsBlank(SearchInput1_1.Text) || Lower('Full Name') Contains Lower(SearchInput1_1.Text)), (CountRows(FilterStatus.SelectedItems) = 0 || Status.Value in FilterStatus.SelectedItems.Result), // Other ComboBox filters would follow a similar pattern... )

  I'm looking for guidance on how to correctly filter the gallery based on both the search input and the selections from multiple ComboBoxes tied to SharePoint choice fields. Any suggestions on how to structure this filter logic or workarounds for the issues I've encountered would be greatly appreciated.

  Thank you in advance for your help!

[v-jefferni]

Hi @zizzer ,

Could you please confirm that all the columns you're trying to use Combo boxes to filter are Choices type in SharePoint? Do they all allow multiple selections? In Items properties of these Combo boxes, are they all the Choices(List.Column) functions?

In addition, how would you like to filter a multiple Choices column based on the selections in one Combo box, exact match or include? For example, in a field of one record there are A,B are selected, when selecting in Combo box, only when A and B are selected, the record can be found, or whether A or B or both is selected the record can be found? 

The formula will vary according to the answers to all above question, please provide specific scenario you want to achieve.

Best regards,

[zizzer]

Hi,

Thanks for you reply.

All are choice types in SP. 2 allow multiple selections: Interests and Availability. The other 2 (Schedule [actually called Schedule Preference] and Status are single choice.

In Powerapps the item property of the comboboxes are: 

Choices([@'Volunteer Database'].'Schedule Preference') for example.

I'd like any combinations to show up -include- so if multiple choices are made from the combobox it doesnt need to be all those choices appear in the gallery item; if choices A and Bare all selected all items that contain any combination of A and/or should appear, not only items that contain both.

[v-jefferni · Accepted solution]

Hi @zizzer ,

OK, I understood. So, please try below formula first:

With(
    {wFilter:Search('Volunteer Database', SearchInput1_1.Text, "Full Name")},
    Filter(
        wFilter As TT, 
        IsBlank(FilterStatus.Selected) || TT.Status.Value in FilterStatus.SelectedItems.Value), 
        IsBlank(FilterSchedule.Selected) || TT.'Schedule Preference'.Value in FilterSchedule.SelectedItems.Value, 
        IsBlank(FilterInterests.Selected) || Sum(ForAll(TT.Interests.Value As AI, If(AI.Value in FilterInterests.SelectedItems.Value, 1, 0)),Value) > 0, 
        IsBlank(FilterAvailability.Selected) || Sum(ForAll(TT.Availability.Value As AA, If(AA.Value in FilterAvailability.SelectedItems.Value, 1, 0)),Value) > 0
    )
)

Best regards, 

[zizzer]

Hi there,

I tried that but it came up with multiple errors including: invalid number of arguments recieved 5, expected 2. and then things like the function sum and forall have invalid arguments, if I browsed through the lines.

[v-jefferni]

Hi @zizzer ,

Could you please post some screenshots? Where does the error invalid number of arguments occur? 

Best regards,

[zizzer]

Hey,

Thanks for your help.

What screenshots would help?

Here is details of where the errors codes appear:

"Invalid number of arguments: recieved 5, expected 2" occurs immeaditely on the first word "With" and remains throughout the code everywhere.

"Search(iVolunteer Database" has 'The function 'Search' has some invalid arguments' -it is because 'The specified column 'Full Name' does not exist. The column with the most similar name si 'Surname'. (but 'Full Name' is recognised in the code I have just for the text search function- that column is a calculated column bring together First Name and Surname.

In this line: " IsBlank(FilterSchedule.Selected) || TT.'Schedule Preference'.Value in FilterSchedule.SelectedItems.Value,"

at the TT it says "Name isn't valid. 'TT' isn't recognized." and 'Invalid use of '.' (cant tell if this is the TT. or .value it remains hovering over both of them.

In this line: IsBlank(FilterInterests.Selected) || Sum(ForAll(TT.Interests.Value As AI, If(AI.Value in FilterInterests.SelectedItems.Value, 1, 0)),Value) > 0,

the same TT and '.' errors and then also "The function 'Sum' has some invalid arguments' and 'The function 'ForAll' has some invalid arguments'. On the same line the Value at this point "1, 0)),Value) " says "Name isn't valid. 'Value' isn't recognized'

The last line has the same errors as above and at this point "AA.Value" It says "Name isn't Valid. 'AA' isn't recognized."

[zizzer]

update-

Singling out the code line by line, this line works when not combined with the other filters:

    CountRows(FilterStatus.SelectedItems) = 0 || Status.Value in FilterStatus.SelectedItems.Value

[v-jefferni]

Hi @zizzer ,

It seems Search function doesn't work on calculated columns, I haven't tested this, but we can use Filter instead. All other errors may caused by the Search function because it's the basic data set to Filter. 

Please try below formula instead:

With(
    {wFilter: Filter('Volunteer Database', (IsBlank(SearchInput1_1.Text) || Lower(SearchInput1_1.Text) in Lower('Full Name')},
    Filter(
        wFilter, 
        (IsBlank(FilterStatus.Selected) || Status.Value in FilterStatus.SelectedItems.Value)
        && 
        (IsBlank(FilterSchedule.Selected) || 'Schedule Preference'.Value in FilterSchedule.SelectedItems.Value)
        && 
        (IsBlank(FilterInterests.Selected) || Sum(ForAll(Interests.Value As AI, If(AI.Value in FilterInterests.SelectedItems.Value, 1, 0)),Value) > 0)
        && 
        (IsBlank(FilterAvailability.Selected) || Sum(ForAll(Availability.Value As AA, If(AA.Value in FilterAvailability.SelectedItems.Value, 1, 0)),Value) > 0)
    )
)

Best regards,

[zizzer · Accepted solution]

Ok,

I have it working:

Filter(

    'Volunteer Database',

    (IsBlank(SearchInput1_1.Text) || StartsWith(Lower('Full Name'), Lower(SearchInput1_1.Text))) &&

    (CountRows(FilterStatus.SelectedItems) = 0 || Status.Value in FilterStatus.SelectedItems.Value) &&

    (CountRows(FilterSchedule.SelectedItems) = 0 || 'Schedule Preference'.Value in FilterSchedule.SelectedItems.Value) &&

    (CountRows(FilterInterests.SelectedItems) = 0 || Not(IsEmpty(Filter(Interests, Value in FilterInterests.SelectedItems.Value)))) &&

    (CountRows(FilterAvailability.SelectedItems) = 0 || Not(IsEmpty(Filter(Availability, Value in FilterAvailability.SelectedItems.Value)))

)

)

I actually used your first suggestion and with some trouble shooting modified that a little- accepting that as answer as it was what set me on the right path. 

Huge thanks, I was at a dead end and needed some new suggestions.