How Can I Apply Multiple Filters to a SharePoint List in PowerApps? SOLVED

[NewLearner999]

Hello Community,

I'm new to PowerApps and desperately need your help. I have pulled a SharePoint list. I need to apply 4 filters to this list, and I'm struggling to get the desired results. Here's what I'm trying to achieve:

1. Name Filter: Text search on the Title column in SharePoint.
2. Product Owner Filter: A person selection in SharePoint, implemented as a combobox (single select)
3. Type Filter: A Choices selection filter using a combobox (single choice).
4. Status Filter: Another Choices selection filter using a combobox (single choice).

Users can apply any, all, or none of these filters, and the result should generate the filtered list accordingly. If no filters are applied, it should show the whole list.

Here's what I've tried so far (please see the screenshot and formulas below):

Filter('Trading and Optimisation RAID+D',And(Or(IsBlank(TextInputTitle.Text), StartsWith('Title',TextInputTitle.Text)), Or(IsEmpty(ComboBoxProductOwner.SelectedItems), 'Product Owner'.DisplayName In ComboBoxProductOwner.SelectedItems.Result),Or(IsEmpty(ComboBoxType.SelectedItems), Type.Value In ComboBoxType.SelectedItems.Value), Or(IsEmpty(ComboBoxStatus.SelectedItems),'Status'.Value In ComboBoxStatus.SelectedItems.Value)))

 

Filter(Search('Trading and Optimisation RAID+D',TextInputTitle.Text,'Title'), 'Product Owner'.DisplayName In ComboBoxProductOwner.Selected.Value ||IsBlank(ComboBoxProductOwner.Selected.Value), "Item Type".DisplayName In  ComboBoxType.Selected.Value ||IsBlank(ComboBoxType.Selected.Value), "Status".DisplayName In  ComboBoxStatus.Selected.Value ||IsBlank(ComboBoxStatus.Selected.Value)

 

 

Filter(Search('Trading and Optimisation RAID+D', And(StartsWith(Title, TextInputTitle.Text), Or(IsEmpty(ComboBoxProductOwner.SelectedItems), 'Product Owner'.DispayName In ComboBoxProductOwner.SelectedItems.Result), Or(IsEmpty(ComboBoxType.SelectedItems), Type.Value In ComboBoxType.Selected Items.Value), Or(IsEmpty(ComboBoxStatus.SelectedItems), 'Status'.Value In ComboBoxStatus.SelectedItems.Value)))

 

ClearCollect(FilteredRAID,Filter('Trading and Optimisation RAID+D',(Title = TextInputTitle.Text || IsBlank(TextInputTitle.Text)) && (ProductOwner.DisplayName = ComboBoxProductOwner.Selected.DisplayName || IsBlank(ComboBoxProductOwner.Selected)) && ('Item type'.Value = ComboBoxType.Selected.Value || IsBlank(ComboBoxType.Selected)) && ('Status'.Value = ComboBoxStatus.Selected.Value || IsBlank(ComboBoxStatus.Selected)))) ;

 

ClearCollect(FilteredRAID,Filter('Trading and Optimisation RAID+D',

(Title = TextInputTitle.Text || IsBlank(TextInputTitle.Text)) &&

(ProductOwner.DisplayName = ComboBoxProductOwner.Selected.DisplayName || IsBlank(ComboBoxProductOwner.Selected)) &&

(Item type.Value = ComboBoxType.Selected.Value || IsBlank(ComboBoxType.Selected)) &&

(Status.Value = ComboBoxStatus.Selected.Value || IsBlank(ComboBoxStatus.Selected))));

Unfortunately, the above attempts have not yielded the desired results. I'm reaching out to see if anyone in the community has successfully implemented multiple filters like these in PowerApps with a SharePoint list. Any guidance on how to correctly write these formulas or any best practices would be immensely appreciated.

Thank you in advance for your support and expertise!

Solution

@Amik has provided the following formula which resolved my issue!

Filter(
    'Trading and Optimisation RAID+D',
    Len(TextInputTitle.Text) = 0 || Title = TextInputTitle.Text,
    Len(ComboBoxProductOwner.Selected.Value) = 0 || 'Product Owner'.DisplayName = ComboBoxProductOwner.Selected.Value,
    Len(ComboBoxType.Selected.Value) = 0 || Type.Value = ComboBoxType.Selected.Value,
    Len(ComboBoxStatus.Selected.Value) = 0 || Status.Value = ComboBoxStatus.Selected.Value
)

 Formula for the Person filter selection was

Distinct('Trading and Optimisation RAID+D', 'Product Owner'.Display Name)

[Amik]

Hi @NewLearner999 ,

Assuming the Status and Type Choice fields are single select Choice fields in SharePoint and their associated Combo Boxes are also single choice, you can try:

Filter(
    'Trading and Optimisation RAID+D',
    Len(TextInputTitle.Text) = 0 || Title = TextInputTitle.Text,
    Len(ComboBoxProductOwner.Selected.DisplayName) = 0 || 'Product Owner'.DisplayName = ComboBoxProductOwner.Selected.DisplayName,
    Len(ComboBoxType.Selected.Value) = 0 || Type.Value = ComboBoxType.Selected.Value,
    Len(ComboBoxStatus.Selected.Value) = 0 || Status.Value = ComboBoxStatus.Selected.Value
)

Or use StartsWith for the Title search:

Filter(
    'Trading and Optimisation RAID+D',
    StartsWith(Title, TextInputTitle.Text),
    Len(ComboBoxProductOwner.Selected.DisplayName) = 0 || 'Product Owner'.DisplayName = ComboBoxProductOwner.Selected.DisplayName,
    Len(ComboBoxType.Selected.Value) = 0 || Type.Value = ComboBoxType.Selected.Value,
    Len(ComboBoxStatus.Selected.Value) = 0 || Status.Value = ComboBoxStatus.Selected.Value
)

Or use non-delegable Search:

Search(
  Filter(
    'Trading and Optimisation RAID+D',
    Len(ComboBoxProductOwner.Selected.DisplayName) = 0 || 'Product Owner'.DisplayName = 
    ComboBoxProductOwner.Selected.DisplayName,
    Len(ComboBoxType.Selected.Value) = 0 || Type.Value = ComboBoxType.Selected.Value,
    Len(ComboBoxStatus.Selected.Value) = 0 || Status.Value = ComboBoxStatus.Selected.Value
    ),
    TextInputTitle.Text,
   "Title"
)

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

Imran-Ami Khan

[NewLearner999]

Hello Imran-Ami

Thank you so much for taking the time to help me, I appreciate it very much.

I tried the formulas you've helped me with, but there are a number of errors. For the first two, the error states:

Name isn't valid. 'DisplayName' isn't recognised.

This error appears most commonly when a formula refers to something that no longer exists (for example, a control that you've deleted)

The third formula came back with a list of errors, see attached. 

I can confirm that Status and Type Choice fields are single select Choice fields in SharePoint and their associated Combo Boxes are also single choice.

From my research it seems that using the Person or Group Type seem to cause issues.

[Amik]

@NewLearner999 please share a screenshot of the expression box together with the error message.

[NewLearner999]

Hello @Amik 

I can confirm 'Status' and 'Type Choice' fields are single choice fields. 'Product Owner' is also a single choice person field.

I have used the first formula, modified slightly to reflect the column names I have in Sharepoint.

Filter(
    'Trading and Optimisation RAID+D',
    Len(TextInputTitle.Text) = 0 || Title = TextInputTitle.Text,
    Len(ComboBoxProductOwner.Selected.DisplayName) = 0 || 'Product Owner'.DisplayName = ComboBoxProductOwner.Selected.DisplayName,
    Len(ComboBoxType.Selected.Value) = 0 || Item type.Value = ComboBoxType.Selected.Value,
    Len(ComboBoxStatus.Selected.Value) = 0 || Status.Value = ComboBoxStatus.Selected.Value)

I attach the errors for the first formula in this response. 

[NewLearner999]

For Formula 2, I have followed your formula with amendments to reflect the column headers in Sharepoint. Attached error messages, some of them come up several times.

Filter(
    'Trading and Optimisation RAID+D',
    StartsWith(Title, TextInputTitle.Text),
    Len(ComboBoxProductOwner.Selected.DisplayName) = 0 || 'Product Owner'.DisplayName = ComboBoxProductOwner.Selected.DisplayName,
    Len(ComboBoxType.Selected.Value) = 0 || Item type.Value = ComboBoxType.Selected.Value,
    Len(ComboBoxStatus.Selected.Value) = 0 || Status.Value = ComboBoxStatus.Selected.Value
)

[NewLearner999]

For formula 3, again I've followed your formula with amendments to reflect the column headers in Sharepoint. Attached error messages. I cannot thank you enough for your help on this.

Search(

  Filter(

    'Trading and Optimisation RAID+D',

    Len(ComboBoxProductOwner.Selected.DisplayName) = 0 || 'Product Owner'.DisplayName =

    ComboBoxProductOwner.Selected.DisplayName,

    Len(ComboBoxType.Selected.Value) = 0 || Item type.Value = ComboBoxType.Selected.Value,

    Len(ComboBoxStatus.Selected.Value) = 0 || Status.Value = ComboBoxStatus.Selected.Value

    ),

    TextInputTitle.Text,

   "Title"

)

[Amik]

Hi @NewLearner999 - please share a screenshot of entire expression box together with the error message (hover your mouse over the red line and then take a screenshot of the error message).

For example, I want to see something like this in your screenshot:

[NewLearner999]

@Amik , thank you for explaining! I attach the screenshots for the three search formula. Thank you again for your help! 

[Amik]

@NewLearner999 - please confirm what you have in the Items property of CombBoxProductOwner.