Answered

Stacking Filters on a SharePoint List

(0) Share

Report

Posted on by nodonnell

I've been looking around for a solution to this but can't seem to find anything. I'm trying to filter a SharePoint List with a combo box (which allows multiple selections) to apply multiple filters at a time to the data source. This is how it is currently set up and it works fine, however, each filter I apply overwrites the next due to the OR condition.

Any thoughts on how I might alter the code to allow for the filters to be stacked?

Filter(

[@'SP List'],

IsBlank(cboFilter.SelectedItems.Value) Or IsEmpty(cboFilter.SelectedItems) Or

If(cboFilter.Selected.Value="My Advisories",'Modified By'.DisplayName=Office365Users.MyProfile().DisplayName) Or

If(cboFilter.Selected.Value="Approval Pending",ApprovalStatus="Pending") Or

If(cboFilter.Selected.Value="Not Sent",!EmailSent) Or

If(cboFilter.Selected.Value="Active Items",IsActive)

)

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

mdevaney 29,989 Moderator on at

Like (1)

Report
@nodonnell
I think the IF statement can be removed...

Filter( [@'SP List'], IsBlank(cboFilter.SelectedItems.Value)=Blank Or IsEmpty(cboFilter.SelectedItems) Or (cboFilter.Selected.Value="My Advisories" And 'Modified By'.DisplayName=Office365Users.MyProfile().DisplayName) Or (cboFilter.Selected.Value="Approval Pending" And ApprovalStatus="Pending") Or (cboFilter.Selected.Value="Not Sent" And !EmailSent) Or (cboFilter.Selected.Value="Active Items" And IsActive) )

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
nodonnell 17 on at

Like (0)

Report

Thanks for the suggestion, @mdevaney. I gave it a try but I still don't get the stacking effect that I'm looking for. For example, if I want to see unsent items that are active, I should be able to apply both the "active items" filter and also the "not sent" filter. Currently, whichever filter is applied last is taking precedence so if I apply "active items" it filters properly but when I then add the "not sent" filter as well, the filtered data shows unsent items but it includes ALL unsent items, instead of just active unsent items. I hope that makes sense.

Was this reply helpful? Yes No
JR-BejeweledOne 5,836 Moderator on at

Like (1)

Report

Try removing the Or statements and write it like this:

Filter(

[@'SP List'],

IsBlank(cboFilter.SelectedItems.Value Or IsEmpty(cboFilter.SelectedItems), Filter statement,

cboFilter.Selected.Value = "My Advisories" And 'Modified By'.DisplayName = Office365Users.MyProfileV2().DisplayName, Filter Statement,

cboFilter.Selected.Value = "Approval Pending" And ApprovalStatus = "Pending", FilterStatement,

cboFilter.Selected.Value = "Not Sent" And !EmailSent, Filter Statement,

cboFilter.Selected.Value = "Active Items" And IsActive, Filter Statement

)

This will cause your filter to evaluate each set of conditions and apply the correct filtering when it finds the statement that matches. You will have to add some sort of filter to the end of each of these for it to work correctly. Here is an image from one of my apps to give you an idea.

Was this reply helpful? Yes No
Verified answer

nodonnell 17 on at

Like (1)

Report

Thanks for the suggestion @JR-BejeweledOne. Unfortunately, it didn't work out the way I was envisioning it. After lots more trial and error, I discovered that what I really needed was nested Filter() functions. In the end, this was going to be too complex for my novice skills, so I redesigned my filter. I broke out the IsActive filter into its own separate toggle-controlled filter and then removed the multi-select capabilities of the combo box for the 3 other filters to reduce the complexity. I then nested a Filter() function inside another to allow additional filters to be applied to active items only. I also adjusted the code to avoid delegation issues (including the dreaded Boolean issue with SharePoint). In the end, this is what I ended up with, which seems to work well:

If(
IsBlank(varFilter) And boolActiveRecords,
Filter(
[@'IT Advisory List'],
IsActive = "Yes"
),
boolActiveRecords,
Filter(
Filter(
[@'IT Advisory List'],
Or(
varFilter = "My Advisories" And 'Modified By'.DisplayName = varUserName,
varFilter = "Pending Approval" And ApprovalStatus = "Pending",
varFilter = "Not Sent" And ApprovalStatus = "Approved" And EmailSent = false
) ),
IsActive = "Yes"
),
!boolActiveRecords,
Filter(
[@'IT Advisory List'],
IsActive = "No"
) )

Was this reply helpful? Yes No
JR-BejeweledOne 5,836 Moderator on at

Like (0)

Report

Glad you figured it out. This is the best part of this community. You take what people give you and use it to figure out your own solutions and learn a whole lot in the process.

Was this reply helpful? Yes No