Part of this search formula cannot be evaluated due to service limitations

[ammarfassy]

Dears,

I am getting the below errror:

Suggestion: Part of this search formula cannot be evaluated due to service limitations. The local evaluation may produce suboptimal or partial results. If possible simplify the formula. For more information, please see the Search function documentation 

the forumula which i am using is:

Distinct(Filter('PharmaApp-MainMapperExcel', Medical_x0020_Rep_x003a_Email.Value=UserMainEmail), Hospital.Value)

the field Medical_x0020_Rep_x003a_Email is a lookup field on the list. if i use a colum other than a lookup column formula is fine. But i have to filter the data based on the medical reps email.

Please help me. 

[Jeff_Thorpe · Accepted solution]

That error should also cause a blue dot to show up because the query isn't delegable. I am guessing that your data source is SharePoint because SharePoint has many field types that are considered complex (Choice, Lookup, Date, Person/Group, Metadata and etc) and are not delegable.  In this case it looks like the Medical_x0020_Rep_x003a_Email is a choice or lookup field which will cause the non-delegable query warning.

If your list size is under 2,000 then you have some options. The default non-delegable query row limit is 500 but that can be increased to 2,000 by using the setting listed on the article link below.

PowerApps data row limit for non-delegable queries

[Shanescows · Accepted solution]

Everything Jeff said. I will just pile on.

Basically, I have tried to stop using LookUp columns in SharePoint all together with PowerApps. There are just a bunch of gotchas. What you can do is make the LookUp functionality in your App. You can connect to multiple lists and drive your dropdown off the data from the other list. In the App it all works the way you expect and no more delegation issues. The only time this doesn't work is if you need the lookup functionality to work while someone is using SharePoint instead of your App. I have videos on all of this if you need.

[ammarfassy]

thanks a lot Jeff for your valuable feedback. yeah its a lookup field. 

[ammarfassy]

thanks a lot Shane for your valuable feedback. appreicate if you can share the videos with me.  

[Shanescows]

PowerApps Filter Function - That video will explain the exact issue you are running into. 

PowerApps Cascading dropdowns - That video will show you cascading dropdowns which I think will help you connect the dots to recreating LookUp like functionality. 

Enjoy 

[ammarfassy]

thank you Shane.