Answered

How to avoid delegation issues with search and Filter together?

(0) Share

Report

Posted on by CU19051130-0

I am working on a Canvas App in Microsoft Power Apps with a large SharePoint list. I am using both Search() and Filter() functions together to allow users to search and filter records dynamically.
However, I am getting delegation warnings, and the app is not returning all records correctly when the data grows beyond the delegation limit.
I would like to know:
• What is the best way to combine Search and Filter without delegation issues?
• Are there any alternative approaches or optimized formulas?
• Should I switch to StartsWith() or another method for better performance?
Any suggestions or best practices would be appreciated.

Categories:

General topics

I have the same question (0)

All responses (5)

Answers (3)

Sort by

Verified answer

WarrenBelz 155,838 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @CU19051130-0,

What you actually mean is you want Delegation (not avoid it) in your query. Firstly Search() is not Delegable with SharePoint, but a lot of Filter expressions are, so you may be able to leverage that if you have a Delegable filter. I have a blog explaining this.

As an example if you had a Status field and Active records were less than 2,000, you would set your Data Row Limit to this (or whatever in excess of the standard 500 were likely to be Active) and do somthing ilke this
With( { _Data: Filter( SPListName, Status = "Active" ) }, Search( _Data, YourSearchBox.Text, YourFieldNameToBeSearched ) )

Providing you return less than your Data Row Limitfrom the top filter, the Search() functiom will execute locally without any Delegation limitations.

You can also use StartsWith if you want a fully delegable filter
Filter( SPListName, StartsWith( YourFieldNameToBeSearched, YourSearchBox.Text ) )

however as you would realisethis only searches for the string entered at the start of the field rather than anywhere in it.

Please ✅ Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider answering Yes to Was this reply helpful? or give it a Like ♥
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No
MarkRahn 1,418 Super User 2026 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @CU19051130-0

@WarrenBelz is pointing you in the right direction with excellent examples. His post With() Statement managing Delegation – Practical Power Apps is awesome and explains one of the most power underrated or unknown functions in Power FX - the With() function.

If you are using SharePoint as your data source, it has a limited number of operations that can be delegated. SharePoint supports so much more but the Power Platform doesn't expose it. Here is a link to MS Learn that shows what can be delegated in SharePoint: Connect to SharePoint from a canvas app - Power Apps | Microsoft Learn

Another tip is to ensure that the columns you want to filter on are Indexed in SharePoint. SharePoint delegation requires the filtered column to be indexed. Index the columns used in StartsWith() or equality comparisons.

For a multi-column search, you could use OR and StartsWith() like this:

Filter(
MyList,
StartsWith(Title, txtSearch.Text) ||
StartsWith(Description, txtSearch.Text) ||
StartsWith(Author, txtSearch.Text)
)

This community is supported by individuals freely devoting their time to answer questions and provide support. They do it to let you know you are not alone. This is a community.

If someone has been able to answer your questions or solve your problem, please click Does this answer your question. This will help others who have the same question find a solution quickly via the forum search.

If someone was able to provide you with more information that moved you closer to a solution, throw them a Like. It might make their day. 😊

Thanks
-Mark

Was this reply helpful? Yes No
Verified answer

Nanit Khanna 68 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @CU19051130-0,

Search() is not delegable with SharePoint, so for large lists it is better to use Filter() with StartsWith() where possible.
Best practices:
• Use delegable Filter() conditions first
• Apply Search() only on a smaller filtered dataset
• Prefer StartsWith() instead of Search() for better delegation
• Index SharePoint columns used in filters/search
• Keep Data Row Limit high enough for expected filtered results

Example:
Filter(
MyList,
StartsWith(Title, txtSearch.Text)
)

Or for multiple columns:
Filter(
MyList,
StartsWith(Title, txtSearch.Text) ||
StartsWith(Description, txtSearch.Text)
)

You can also use With() to first create a delegable filtered dataset and then apply Search() locally.

Was this reply helpful? Yes No
Verified answer

deepakmehta13a 369 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,

Search() is not delegable in SharePoint, so for large lists it is better to minimize or avoid it.

Recommended approach:
• Use delegable Filter() first
• Then apply Search() locally on the smaller dataset
• Prefer StartsWith() for better delegation support
• Index columns used in filtering/searching

Example using StartsWith():
Filter(
MyList,
StartsWith(Title, txtSearch.Text) &&
Status = "Active"
)

Alternative method using With():
With(
{
_Data: Filter(MyList, Status = "Active")
},
Search(_Data, txtSearch.Text, Title)
)

Best practice:
• Keep the filtered dataset below delegation limits
• Use Dataverse or SQL if advanced searching is required at scale

✅ If this helped, please Accept as Solution to help others ❤️. A Like is appreciated.

Was this reply helpful? Yes No
WarrenBelz 155,838 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @CU19051130-0,

A quick follow-up to see if you received the answer you were looking for. Happy to assist further if not.

Please ✅ Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider answering Yes to Was this reply helpful? or give it a Like ♥
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No