cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarownIOM
Helper I
Helper I

Gallery.Items Filter with multiple conditions not working

I have a PowerApp with a Gallery.  The Gallery uses a complicated Items filter which stopped working after I added an additional field to my sharepoint list.

 

The Filter uses multiple conditions which both work individually -- but if I try to use them both at the same time it returns nothing.

 

Here's the portion of my filter showing only the section that is causing a problem:

 

 

Filter(ECOR, User().Email = Author.Email, Status.Value in "New,Plant")

 

 

I have also tried using && with the same results.

 

As I mentioned this was working before I added another field to "MyList".

Author seems to be the same as 'Created By'.  Both seem to be interchangeable and produce correct results.

Status is a single-choice field.

I sometimes get a warning that the "IN" operator may not work with large datasets.  There are currently 60 records in my list.

 

This has been very frustrating.  I closed (Chrome) and opened Edge and tested with the same results.

Please help!

 

Just for kicks I'll show my complete gallery.items -- but the above code shows the area where the problem is.

 

 

If(chkShowCompleted.Value, Filter(Search(ECOR, txtSearch.Text, "Title","Details","Item"), Status.Value in "Approved,Rejected"),

  If(Or(IsBlank(CurrentUser), varMyEcors), 
  Filter(Search(ECOR, txtSearch.Text, "Title","Details","Item"), User().Email = Author.Email, Status.Value in "New,Plant,R&D,Regulatory"), 

    If(CurrentUser.Group.Value = "Regulatory", 
    Filter(Search(ECOR, txtSearch.Text, "Title","Details","Item"), Status.Value = "Regulatory", Status.Value in "New,Plant,R&D,Regulatory"),

      If(CurrentUser.Group.Value = "R&D", 
      Filter(Search(ECOR, txtSearch.Text, "Title","Details","Item"), Status.Value = "R&D", Status.Value in "New,Plant,R&D,Regulatory"),
      
        Filter(Search(ECOR, txtSearch.Text, "Title","Details","Item"), CurrentUser.Group.Value in Concat(Plants.Value,Value,", ") && Status.Value = "Plant") 
      )
    )
  )
)

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
MarownIOM
Helper I
Helper I

Wow!

I finally figured out the problem.

 

User().Email = "MyEmail@company.com"
Author.Email = "myemail@company.com"

 

So I revised the Filter/Search in the Gallery to use lower case on both user and author as so:

Filter(Search(ECOR, txtSearch.Text, "Title", "Details", "Item"), 
Status.Value in "New, Plant, R&D, Regulatory", 
Lower(User().Email) = Lower(Author.Email))

and the "IN" statement works as it should too.

View solution in original post

4 REPLIES 4
Ben_Hoople
Advocate I
Advocate I

Kind of sounds like the problem is with the comma in the second filter i.e. "New,Plant"

i would try this:

Filter(ECOR, 
   User().Email = Author.Email,
   OR(
      Status.Value in "New",
      Status.Value in "Plant")
)

 

Thanks for the reply.

I had to change the IN to = but that worked; however, that doesn't explain why the IN clause is no longer working.

Filter(ECOR, Or(Status.Value = "New", Status.Value = "Plant"), User().Email = 'Created By'.Email)

Also the IN statement worked by itself just not with the User().email formula.

powerapps-filter-in-issue.jpg

 

MarownIOM
Helper I
Helper I

Wow!

I finally figured out the problem.

 

User().Email = "MyEmail@company.com"
Author.Email = "myemail@company.com"

 

So I revised the Filter/Search in the Gallery to use lower case on both user and author as so:

Filter(Search(ECOR, txtSearch.Text, "Title", "Details", "Item"), 
Status.Value in "New, Plant, R&D, Regulatory", 
Lower(User().Email) = Lower(Author.Email))

and the "IN" statement works as it should too.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (5,632)