cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mokhawaja
Kudo Collector
Kudo Collector

Filter function become non delegable when using a lookup field

The filter function becomes non delegable when filtering using the complex type lookup field in CDS and shows the blue dot...
 

Screenshot (21).png

 

 

 
in the above screenshot... the entity contains more than 2000 records, and to test the filter function i tested it against data that falls after the first 500 records and put only 5 records that meet the criteria in the first 500 records... and the result was only the first 5 records that are in the first 500!!!
 
i checked the delegation article in the documentation and could not find any information about this limitation in the filter function (which could also be present in other functions like lookup and sort!!)
 
so please guys... help us on this issue and recheck the documentation.

 

best regards,

Mohammad

17 REPLIES 17

Hi @mokhawaja,

 

I believe I found the solution after reading this article from Microsoft:

 

https://powerapps.microsoft.com/en-us/blog/support-for-lookups/

 

Essentially, the .Value property of the choice field has to be used so my simple example is:

 

Filter(Projects,ProjectType_New.Value = SearchProjectInput.Text)

 

This works in my more complex formula:

 

Filter(Projects,Or(IsBlank(SearchProjectInput.Text),SearchProjectInput.Text in Text(ProjectManager_),Or(IsBlank(SearchProjectInput.Text),SearchProjectInput.Text in Text(ATRF_New), Or(IsBlank(SearchProjectInput.Text),SearchProjectInput.Text in Text(ProjectTitle_),Or(IsBlank(SearchProjectInput.Text),SearchProjectInput.Text in Text(ProjectKeywords_),Or(IsBlank(SearchProjectInput.Text),SearchProjectInput.Text in Text(ProjectSponsor_),Or(IsBlank(SearchProjectInput.Text),SearchProjectInput.Text in Text(LessonsLearnedDate_),Or(IsBlank(SearchProjectInput.Text), ProjectType_New.Value = SearchProjectInput.Text)))))))))

 

Thanks again for your help. I will mark this as solved!

 

 

Yes, and hi @v-micsh-msft , I'm experiencing the same frustration as @mokhawaja with the non-delegable operators. I reached the 500 recored limit in less than 3 hours today and I want to filter on these records. What's even worse is that the filter function applies the oldest records first which means the new data entered is again, unfortunately irrelevant.

 

Any update on this is appreciated. I've heard rumors that this may be resolved soon for CDS, but would the "fix" apply to SharePoint lists?

@v-micsh-msft, we'd love an update as to when (or if) search functions will be delegable and query beyond the 500 record limit. This is a very frustrating limitation and restricts my ability to develop using Power Apps.

For those following this string, Microsoft has published an article indicating which functions/operators are delegable:

 

https://powerapps.microsoft.com/en-us/tutorials/delegation-list/

 

They do not however indicate which types of fields are non-delegable. I have a formal inquiry w/ Microsoft regarding this and will post to this string as soon as I hear back.

@v-micsh-msft, has there been any update or plan to make SharePoint list filter functions delegable? Let us know and thanks.

I'm also waiting for these delegation limitations to be addressed. Those have already caused several issues in my projects where SharePoint lists are used as data sources.

JenVitug
Kudo Commander
Kudo Commander

I am having the same issue, Filter function becoming non delegable and dotted blue warning is showing 😞 😞 😞

drewortiz
Regular Visitor

I wanted to contribute an example, as I've hit this issue multiple times. My scenario involves:

  • I have a 'Region' dropdown which will filter a gallery. 
  • MyTable (SharePoint list) has a lookup field, LawLookupField into another list, 'Law Table'.
  • filteredLaws used as the Items value for my gallery. 
  • Both MyTable and Law Table have been added as data sources to the app.

When the user selects a value from the Region dropdown, On Change is set to this code:

 

ClearCollect(filteredLaws, Filter(
 AddColumns(Choices('MyTable'.'LawLookupField'),"T",
  LookUp([@'Law Table'],Title=Value )),T.Region.Value=dropdownRegion.Selected.Value) )

All the columns from the Law Table are listed under the column filteredLaws.T. For to display description from the Law Table in my gallery use "T.Description". I don't get the delegation warning, but I have not fully tested this on different sized datasets. I hope this helps.

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,671)