cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Kudo Kingpin
Kudo Kingpin

Multiple filters and / or

I have a list with 8 reviewer columns (namely Reviewer1, Reviewer2, etc.). The gallery is currently filtered as such:

Filter(Routing, 
'Reviewer1'.Email=User().Email || 
'Reviewer2'.Email=User().Email || 
'Reviewer3'.Email=User().Email || 
'Reviewer4'.Email=User().Email || 
'Reviewer5'.Email=User().Email || 
'Reviewer6'.Email=User().Email || 
'Reviewer7'.Email=User().Email || 
'Reviewer8'.Email=User().Email)

Is there an easier way to filter the items to return only if current user is one of the reviewers?  The above formula works but I now need to add  another column in the condition (i.e. Status != completed and current user is one of the reviewers).

I originally have one (1) reviewer column (people picker) allowing multiple values -- but I can't get it to work on serial review.  If reviewer1 approves, it goes to reviewer2. Otherwise, reviewer1 can return to author for edits or forward to another (separate from existing reviewers) person/group for review that does not require approval.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Dual Super User III
Dual Super User III

Re: Multiple filters and / or

Hi @Tamras 

 

Please use the below expression to search current user in all the approver columns:
 
Filter(Routing, User().Email in ['Reviewer1'.Email,'Reviewer2'.Email,'Reviewer3'.Email,'Reviewer4'.Email,'Reviewer5'.Email,'Reviewer6'.Email,'Reviewer7'.Email,'Reviewer8'.Email])
 
Hope this helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

Highlighted
Community Support
Community Support

Re: Multiple filters and / or

Hi @Tamras ,

Are these Reviewer1, Reviewer2, Reviewer3, ... columns all Person type columns in your SP list?

 

Based on the needs that you mentioned, I agree with @yashag2255 's thought almost. Please consider modify your formula as below:

 

Filter(
Routing, User().Email in ['Reviewer1'.Email, 'Reviewer2'.Email, 'Reviewer3'.Email, 'Reviewer4'.Email, 'Reviewer5'.Email, 'Reviewer6'.Email, 'Reviewer7'.Email, 'Reviewer8'.Email],
Status <> "Completed" )

Note: The != operator is not supported in PowerApps, please use the <> operator.

Above formula may cause a Delegation warning issue, if the amount of your SP List records is not more than 2000, you could ignore this warning issue.

If you want to get rid of this Delegation warning issue, please take a try with the following workaround:

Set the OnStart property of the App control to following:

ClearCollect(RecordsCollection, Routing)

Set the Items property of the Gallery to following:

Filter(
       RecordsCollection, 
       User().Email in ['Reviewer1'.Email, 'Reviewer2'.Email, 'Reviewer3'.Email, 'Reviewer4'.Email, 'Reviewer5'.Email, 'Reviewer6'.Email, 'Reviewer7'.Email, 'Reviewer8'.Email],
       Status <> "Completed"
)

If the amount of your SP list records is more than 200, please check and see if the following solution would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M7...

 

Best regards,

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
Highlighted
Dual Super User III
Dual Super User III

Re: Multiple filters and / or

Hi @Tamras 

 

Please use the below expression to search current user in all the approver columns:
 
Filter(Routing, User().Email in ['Reviewer1'.Email,'Reviewer2'.Email,'Reviewer3'.Email,'Reviewer4'.Email,'Reviewer5'.Email,'Reviewer6'.Email,'Reviewer7'.Email,'Reviewer8'.Email])
 
Hope this helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

Highlighted
Community Support
Community Support

Re: Multiple filters and / or

Hi @Tamras ,

Are these Reviewer1, Reviewer2, Reviewer3, ... columns all Person type columns in your SP list?

 

Based on the needs that you mentioned, I agree with @yashag2255 's thought almost. Please consider modify your formula as below:

 

Filter(
Routing, User().Email in ['Reviewer1'.Email, 'Reviewer2'.Email, 'Reviewer3'.Email, 'Reviewer4'.Email, 'Reviewer5'.Email, 'Reviewer6'.Email, 'Reviewer7'.Email, 'Reviewer8'.Email],
Status <> "Completed" )

Note: The != operator is not supported in PowerApps, please use the <> operator.

Above formula may cause a Delegation warning issue, if the amount of your SP List records is not more than 2000, you could ignore this warning issue.

If you want to get rid of this Delegation warning issue, please take a try with the following workaround:

Set the OnStart property of the App control to following:

ClearCollect(RecordsCollection, Routing)

Set the Items property of the Gallery to following:

Filter(
       RecordsCollection, 
       User().Email in ['Reviewer1'.Email, 'Reviewer2'.Email, 'Reviewer3'.Email, 'Reviewer4'.Email, 'Reviewer5'.Email, 'Reviewer6'.Email, 'Reviewer7'.Email, 'Reviewer8'.Email],
       Status <> "Completed"
)

If the amount of your SP list records is more than 200, please check and see if the following solution would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M7...

 

Best regards,

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Super User II
Super User II

Re: Multiple filters and / or

Filter(Routing, 
               (
'Reviewer1'.Email=User().Email || 'Reviewer2'.Email=User().Email || 'Reviewer3'.Email=User().Email || 'Reviewer4'.Email=User().Email || 'Reviewer5'.Email=User().Email || 'Reviewer6'.Email=User().Email || 'Reviewer7'.Email=User().Email || 'Reviewer8'.Email=User().Email)
)
&&
!(Status="completed")
)

This will be delegable - I assumed your status column is a text field, if not then let me know and we can work out how best to solve that for you.

 

Cheers,

ManCat


@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Highlighted
Kudo Kingpin
Kudo Kingpin

Re: Multiple filters and / or

@v-xida-msft When I tried your solution to get rid of the delegation, my filter returns null. 

In App OnStart, I have:

Concurrent(
    ClearCollect(
        Navigation,
        {MenuItem: "Author"},
        {MenuItem: "Reviewer"},
        {MenuItem: "Document Details"},
        {MenuItem: "Log Out"}
    ),
    ClearCollect(
        RecordsCollection,
        Routing
    )
)

In Gallery item, I have:

Filter(
    RecordsCollection,
    User().Email in [
        'Reviewer1'.Email,
        'Reviewer2'.Email,
        'Reviewer3'.Email,
        'Reviewer4'.Email,
        'Reviewer5'.Email,
        'Reviewer6'.Email,
        'Reviewer7'.Email,
        'Reviewer8'.Email
    ],
    Status <> "Completed"
)

Without using the RecordsCollection the filter returned 1 item (which is right), but with it, it returned none.  Do I only use it when my list reaches more than 200 items? Currently, I only have a handful of items to test with.  

 

Highlighted
Kudo Kingpin
Kudo Kingpin

Re: Multiple filters and / or

@iAm_ManCat Thanks. I think you have an extra ) after Reviewer8 in your formula, but this worked too:

Filter(
    Routing,
    ('Reviewer1'.Email = User().Email || 
     'Reviewer2'.Email = User().Email || 
     'Reviewer3'.Email = User().Email || 
     'Reviewer4'.Email = User().Email || 
     'Reviewer5'.Email = User().Email || 
     'Reviewer6'.Email = User().Email || 
     'Reviewer7'.Email = User().Email || 
     'Reviewer8'.Email = User().Email
     ) 
     && !(Status = "Completed")
)
Highlighted
Super User II
Super User II

Re: Multiple filters and / or

Thanks, yeah you're right 🙂

The idea is still there though - I have to avoid delegation wherever possible as we've based all our apps around SharePoint as the back-end, so unless the list is a static set of options that's never going to grow, we never use 'in' or any other non-delegable query - once you hit 2k records, you'll not only have incorrect results if doing sums or calculation but you'll also miss certain items if they fall above the 2k max limit for items.

 

Doing a clearcollect as suggested above is also not going to work, as it only collects 2k items - unless you do multiple clearcollects and join them for every collection above 2k items - as well as the fact that it will take longer for the app to load when you're pulling in the entire set of records instead of targeting the ones you want. Our apps have to be mobile-compatible so having it pull in all records is not possible due to the extra data overhead and time taken over mobile connection.

 

Cheers,

ManCat


@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Highlighted
Kudo Kingpin
Kudo Kingpin

Re: Multiple filters and / or

@iAm_ManCat 

Any suggestions on how to avoid "delegation?" We are also using SP Online as back end for all apps.  I'm reading more about it but I don't really understand how to avoid it.  

Are we talking about 2k total list items or 2k filtered items?  I don't think this particular filter will return more than 2k at any given time. But if its the totak list items, then yes, I need to avoid delegation.  

Highlighted
Super User II
Super User II

Re: Multiple filters and / or

If you have delegation warnings and your list has >2000 items, then its the first 2000 rows only that you will be filtering against, everything after that is inaccessible from a non-delegable query (unless you've narrowed it down with a delegable query and then are filtering within that subset)

 

If you let me know what parts you are having delegation issues with then I'm sure I can pass on a few ideas - the number one idea is where you use people pickers or dropdowns, have an additional text field that stores the text value of the selected item every time its changed - this can then be filtered as an = "text you want to filter by"


@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Highlighted
Kudo Kingpin
Kudo Kingpin

Re: Multiple filters and / or

@iAm_ManCat  I found your post on another discussion and if I read it right, does it mean I have to create new columns for each existing column I want to use in my filter?  So in above example, my filter has 9 columns...I have to create 9 new columns to store the values? 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (6,813)