Help required with how to combine multiple filters on a Gallery using text input and combo box

[wilsmatt]

Hi,

I'm just starting out with PowerApps and need some help with combining a text input field and two combo boxes to filter a gallery, the items are in a SP list. I want the users to be able to search and filter on a list of users within our company by the following fields in the SP list:

Full Name - textbox

Surname - textbox

Topic - dropdown

Language - dropdown (multi select)

I have the following fields in my PowerApps main screen which has the gallery:

searchinput.text - Search Input Text box (Surname or FullName)

cbTopics - Combo Box pulling the items from the Topics field

cpLangFilter - Combo box pulling the items from the Language field (French, German, Italian, Arabic)

I want to be able to search and filter the gallery using the 3 fields together and also independently, I have the following which is working ok for the text input and cbTopics drop-down:

Filter( 

    'Skills_index', 

    And( 

        Or( 

            IsBlank(cbTopics.SelectedItems), 

            IsEmpty(cbTopics.SelectedItems), 

            Topics.Value in cbTopics.SelectedItems.Value 

        ), 

        StartsWith( 

            FullName, 

            SearchInput.Text 

        ) || StartsWith( 

            Surname, 

            SearchInput.Text) 

    ) 

) 

But I can't get the cbLangFilter to combine with this, so for example if a user has listed "French" and "German" in the Language field in SP, I want to be able to filter the results of the gallery if either language is selected or both and return the results alongside or independently of the other fields.

I suspect I'm just not combining the filters right using "AND" or "OR".

I understand I may need to Concat the Language field as multiple select options are Tables and can't be filtered like a single choice combo?

Thanks in advance for any help or guidance.

Matt

[WarrenBelz]

Hi @wilsmatt ,

Yes, you have a many-to-many filter there (I assume Language is a multi-choice field as is the Combo Box) and these need to be treated in some isolation as below

With(
   {
      wList:
      Filter( 
         'Skills_index',
         (
            Len(cbTopics.Selected.Value) = 0 ||
            Topics.Value in cbTopics.SelectedItems.Value
         ) &&
         ( 
            StartsWith( 
               FullName, 
               SearchInput.Text 
            ) || 
            StartsWith( 
               Surname, 
               SearchInput.Text) 
            )
         )
      )
   },
   Ungroup(
      ForAll(
         cbLangFilter.SelectedItems As aSel,
         Filter(
            wList,
            aSel.Value in Language.Value
         )
      ),
      "Value"
   )
) 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[wilsmatt]

Hi @WarrenBelz 

Many thanks for your help, I've applied your code and the cblangfilter drop down is applying great.

However, the default view of the gallery is 'empty' which I'm assuming is I am somehow blocking that via the filter for all items displaying?. I will only see items displayed if I use the cblangfilter.

Do I need to somehow 'clear' the filters for the default view, so when the drop-downs are empty it resets?. Ideally I would want users to be able to search (filter) using any of the filters - Text, Dropdown or Multi Select drop down.

Also when I apply the code, I get an 'incomparable' type error on the Topics field on the "0" as the combobox values are text I'm assuming?

Many thanks

Matt

[WarrenBelz · Accepted solution]

Hi @wilsmatt ,

Len() is simply Length and covers both IsBlank and IsEmpty. You can try this which should also cover the empty gallery.

With(
   {
      wList:
      Filter( 
         'Skills_index',
         (
            CountRows(cbTopics.SelectedItems) = 0 ||
            Topics.Value in cbTopics.SelectedItems.Value
         ) &&
         ( 
            StartsWith( 
               FullName, 
               SearchInput.Text 
            ) || 
            StartsWith( 
               Surname, 
               SearchInput.Text) 
            )
         )
      )
   },
   If(
      CountRows(cbLangFilter.SelectedItems) = 0,
      wList,
      Ungroup(
         ForAll(
            cbLangFilter.SelectedItems As aSel,
            Filter(
               wList,
               aSel.Value in Language.Value
            )
         ),
         "Value"
      )
   )
) 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[wilsmatt]

@WarrenBelz many thanks that's worked a treat, I'll have a look at your blog as I feel I need to go back to basics a bit too and get some training...many thanks again!