Filtering SharePoint List using Multiple Choice Column

[iramos85]

Hello Everyone,

I am building this app to browse a SharePoint List and select records to view based on criteria/filters from a Search Menu.  I have gone through a myriad of sources looking for my case-use, I feel like I'm *right there* but still missing something!

My app goes as follows:

User selects Combo Box dropdown (column supports and is set for Multiple Choices):

Gallery SHOULD filter based on these two choices.  To do this, I used this code in the "Items" property of my gallery:

    Filter(
        'Report Repository',
        IsBlank(Dropdown_Key_Attributes.SelectedItems.Value) || IsEmpty(Dropdown_Key_Attributes.SelectedItems) || "Key Attributes" 
 = Dropdown_Key_Attributes.SelectedItems.Value
    ,
    Search_Title.Text,'Health Plan')

(Search_Title.Text is a text input field that i'm using to search Record titles as well.)


Right now, nothing comes up in my gallery when I select anything in my Combo Box. The idea is to let users search by 1, 2, 3 or 4 fields to find records. Only one field is a Text Input (Search_Title) and the rest are Combo Boxes (2 Multiple Choice Columns) and one single choice column. I feel like once I get the idea of one Combo Box filter, I'll be able to add the other criteria and FINALLY finish  my search.   

Am I overlooking something or just plain leaving a vital piece of code. I think i've redone this enough times that I properly confused myself. Any support is greatly appreciated!

[WarrenBelz]

Hi @iramos85 ,

You need single quotes around the field name and for multiple choice you need

 'Key Attributes' in Dropdown_Key_Attributes.SelectedItems.Value

Also note that this is not Delegable. However you can get some form of Delegation management with this

With(
   {
      _Data:
      Ungroup(
         ForAll(
            Dropdown_Key_Attributes.SelectedItems As _Data,
            Filter(
               'Report Repository',
               'Key Attributes' = _Data.Value
            )
         ),
         Value
      )
   },
   Search(
      _Data,
      Search_Title.Text,
      'Health Plan'
   )
)

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

[DarogaBabu]

Hi iRamos,

I am fairly new to Power Apps and have run into a similar issue and since this is a multi select combo box, 

Combobox.SelectedItems.Value

is not supposed to get the selected items as we expect. You can check this thread which should answer your question.

Building-Power-Apps/Filter-multiple-selection-choice-column 

[Kellboy2243 · Accepted solution]

Hi @iramos85 

Try below Solution Steps:

1. Understand the Combo Box Output:

   • The SelectedItems property of a Combo Box returns a table. You need to compare each item in this table to the items in your SharePoint list.

2. Adjust the Filter Logic:

   • Use the ForAll function to iterate over the selected items in the Combo Box.
   • Use the Or operator to check if any of the selected items match the items in the SharePoint list.

3. Incorporate Text Search:

   • Combine the multiple choice filter with the text input filter using the And operator.

Example Code

Filter(
    'Report Repository',
    // Check if the Combo Box is empty
    IsBlank(Dropdown_Key_Attributes.SelectedItems) || IsEmpty(Dropdown_Key_Attributes.SelectedItems) ||
    // Iterate through the selected items in the Combo Box
    ForAll(
        Dropdown_Key_Attributes.SelectedItems,
        // Check if the current item matches any of the items in the SharePoint column
        "Key Attributes".Value in Value
    ) &&
    // Check if the text input field matches any part of the titles
    (IsBlank(Search_Title.Text) || Search_Title.Text in Title)
)

Explanation:

1. IsBlank/IsEmpty Check:

   • Ensure that if no items are selected in the Combo Box, the filter doesn't exclude all items.

2. ForAll Function:

   • Iterate over each selected item in the Combo Box (Dropdown_Key_Attributes.SelectedItems).
   • Check if the Value of the selected item exists in the Key Attributes column of the SharePoint list.

3. Text Input Filter:

   • Use the text input filter to match any part of the title (Title).

Final Advice:

• Ensure that the Key Attributes column in SharePoint is properly indexed if the dataset is large to improve performance.
• Test each part of the filter separately to confirm they work as expected before combining them.

By following these steps, your gallery should filter correctly based on the selected items in the Combo Box and the text input field. Please mark this as a solution if this helped you with your issue.

[iramos85]

thanks for the response! 

I implemented this code, but I keep running into an issue with the ForAll() statement.

"Invalid argument type"

and this error:

The implemented code is as follows:

Filter(
    'Report Repository',
    IsBlank(Dropdown_Key_Attributes.SelectedItems) || IsEmpty(Dropdown_Key_Attributes.SelectedItems) || 
    ForAll(
        Dropdown_Key_Attributes.SelectedItems,
         Value in "Key Attributes".Value)
    && 
    (IsBlank(Search_Title.Text) || Search_Title.Text in 'Health Plan'))
)

I was able to get results to show up by passing in a Boolean statement (based on another thread on this topic) with the ForAll() statement:

Filter(
    'Report Repository',
    IsBlank(Dropdown_Key_Attributes.SelectedItems) || IsEmpty(Dropdown_Key_Attributes.SelectedItems) || true in
    ForAll(
        Dropdown_Key_Attributes.SelectedItems,
         Value in 'Key Attributes'.Value)
    && 
    (IsBlank(Search_Title.Text) || Search_Title.Text in 'Health Plan'))

However the issue that I ran into now is that all of my gallery controls throw errors:

ThisItem no longer is recognized! Not sure how this error manifested