cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
renet1234
New Member

Filter gallery fields with Contain. "In" does not work.

Basically, this is my code (the filter is a little longer) but this is it basically.

Im filtering a large SharePoint Document Library by Metadata.

 

Filter

(

      [@'DataSource'], (

     "Filtering first text" = 'Text MetaField'

       And

     CurrentUser.Email = 'Email MetaField'

)

 

 

My problem is that in the Email MetaField,  sometimes there are multiple email addresses in the field, like this: "email01@email.com;email02@email.com"

 

I was looking for a "contains" instead of equal operator for it, but all i could find is "In". 

I tried:  

CurrentUser.Email in 'Email MetaField'

also tried

'Email MetaField' in CurrentUser.Email

 

I dont get a syntax error, but i get no results in either.  - any help is appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@renet1234 

You will NOT be able to use the in operator!!  It is not delegable and thus PowerApps need to pull down all the records it can get (max 2000) in order to try and perform the in operation in app memory. 

So, that is why you are seeing nothing.

 

Your only solution - in app, if you want to use the in operator on that particular field is to devise a pre-filter that IS delegable and will return less than 2000 records.

 

Example:  

With({_preFilter: Filter(yourList, <delegableCriteria>)},
    Filter(_preFilter, CurrentUser.Email in 'Email MetaField')
)

 

If that is not something that you can devise, then the only other option is to take it to the data level...devise a separate list that would normalize the Email MetaField with per-user records that provide the related records in your library list.

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

11 REPLIES 11
RandyHayes
Super User
Super User

@renet1234 

Is 'Email MetaField' a text column?

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
renet1234
New Member

Yes, regular string value field.

RandyHayes
Super User
Super User

@renet1234 

If that is the case, then this formula *should* be providing results:

Filter(DataSource, 
     'Text MetaField' = "Filtering first text", 
     CurrentUser.Email in 'Email MetaField'
)

 

Note, the order of your comparison criteria does make a difference!

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

No results, and get a delegation warning

RandyHayes
Super User
Super User

@renet1234 

Well, you ARE going to get a delegation warning because the in operator is not delegable!

And the next question would be, how many records are in your datasource and what is your record limit in the app set to?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Its a document library with A LOT of docs.. ~16K documents

i set the record limit to 2k.

The query should only bring back at max 15-20 records.

RandyHayes
Super User
Super User

@renet1234 

You will NOT be able to use the in operator!!  It is not delegable and thus PowerApps need to pull down all the records it can get (max 2000) in order to try and perform the in operation in app memory. 

So, that is why you are seeing nothing.

 

Your only solution - in app, if you want to use the in operator on that particular field is to devise a pre-filter that IS delegable and will return less than 2000 records.

 

Example:  

With({_preFilter: Filter(yourList, <delegableCriteria>)},
    Filter(_preFilter, CurrentUser.Email in 'Email MetaField')
)

 

If that is not something that you can devise, then the only other option is to take it to the data level...devise a separate list that would normalize the Email MetaField with per-user records that provide the related records in your library list.

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

THANK YOU for all the help so far. 

Do i put that 'With' statement right before the 'Filter' statement?

i think i got it.  I figured out what you you were trying to show me.. i combined the With Statement to my current filter statement.

I see how it works! thank you!

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors
Users online (1,445)