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

[renet1234]

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.

[RandyHayes]

@renet1234 

Is 'Email MetaField' a text column?

[renet1234]

Yes, regular string value field.

[RandyHayes]

@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!

[renet1234]

No results, and get a delegation warning

[RandyHayes]

@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?

[renet1234]

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 · Accepted solution]

@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.

[renet1234]

THANK YOU for all the help so far. 

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

[renet1234]

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!