Search not working when datasource has more than 2000 records

(2) Share

Report

Posted on by Poweruser32490

126

I have a datasource SharePoint which has around 5K records. The problem is my search works fine when i filter by certain columns. But when i normally search without any filters, search result doesn't come up. May i know how to resolve this?

With(
    {
        _activerequests: SortByColumns(
            Filter(
                'Datasource',
                (Len('Process Type CB'.Selected.Value) = 0 || ChoiceProcessType.Value = 'Process Type CB'.Selected.Value) && (Len('Employee Type CB'.Selected.Value) = 0 || ChoiceResourceType.Value = 'Employee Type CB'.Selected.Value) && (Len('Region CB'.Selected.Title) = 0 || Region_New = 'Region CB'.Selected.Title)
            ),
            "Created",
            SortOrder.Descending
        )
    },
    SortByColumns(
        Filter(
            _activerequests,
            Len(SearchBox.Text) = 0 || SearchBox.Text in HM.DisplayName || SearchBox.Text in DL.DisplayName || SearchBox.Text in FirstName || SearchBox.Text in Title || SearchBox.Text in 'Id' || SearchBox.Text in 'Created By'.DisplayName
        ),
        varsortcolumn,
        varsortdirection
    )
)

Categories:

AI Builder

Building Power Apps

Experimental features

Microsoft Dataverse with Power Apps

Connector development

GCC, GCCH, DoD - Federal App Makers (FAM)

Error handling

Governance & administering

Power Apps Pro Dev & ISV

Power Query

General topics

I have the same question (0)

All responses (6)

Answers (0)

Sort by

Suggested answer

11manish 3,489 on at

Like
a
(0)

Report
Copy link

Link copied!
The primary issue is the use of the in operator, which is non-delegable for SharePoint. For large lists, Power Apps only searches the first portion of the data, which

explains why searches work after filtering but not across the entire list.

If you need to search all 5,000+ records, the best approaches are:

Replace in with StartsWith() where possible.

Apply delegable filters before searching.

If full-text "contains" searches across large datasets are a business requirement, consider moving the data to Dataverse or SQL Server, which are better suited for enterprise-scale search.

Was this reply helpful? Yes No
Suggested answer

Valantis 6,823 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @Poweruser32490,

The in operator is non-delegable to SharePoint so your search only runs against the first 2000 records, which is why filters work (they use delegable equality checks) but open search doesn't.

Fix: load all records into a collection on App OnStart:

ClearCollect(colRequests, 'Datasource')

Then replace 'Datasource' in your formula with colRequests. The in operator runs locally against the collection with no delegation limit.

For 5k records this is fine. The collection loads once and your combobox filters + search box all run locally against it instantly.

Best regards,

Valantis ✅ If this helped solve your issue, please Accept as Solution so others can find it quickly.

❤️ If it didn’t fully solve it but was still useful, please click “Yes” on “Was this reply helpful?” or leave a Like :).

🏷️ For follow-ups @Valantis.

📝 https://valantisond365.com/ 💼 LinkedIn ▶️ YouTube

Was this reply helpful? Yes No
CU05070959-0 2 on at

Like
a
(0)

Report
Copy link

Link copied!

You can try using a collection by loading the SharePoint data into it and then using the collection as your data source for the search. Since the search runs against a local collection, it should work without delegation issues. However, for SharePoint lists with a large number of records, I'd recommend using StartsWith() instead, as it's a delegable function and provides better performance.

Tip: If you decide to use a collection, consider using an Index column to load the data in batches. Also, collect only the columns you actually need for filtering, searching, or displaying in the gallery. This helps improve performance and reduces memory usage.

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

Like
a
(0)

Report
Copy link

Link copied!
Hi @Poweruser32490,

Firstly I need to clarify that a Collection will not solve the issue here as it is also limited to 2,000 records. You can look at a multi-phase collection (see this blog of mine), however you need a "Shadow ID" numeric field, which is easy enough to generate (including "back filling" with Flows if you want to head that direction).
With( { _activerequests: SortByColumns( Filter( 'Datasource', ( Len('Process Type CB'.Selected.Value) = 0 || ChoiceProcessType.Value = 'Process Type CB'.Selected.Value ) && ( Len('Employee Type CB'.Selected.Value) = 0 || ChoiceResourceType.Value = 'Employee Type CB'.Selected.Value ) && ( Len('Region CB'.Selected.Title) = 0 || Region_New = 'Region CB'.Selected.Title ) ), "Created", SortOrder.Descending ) }, SortByColumns( Filter( _activerequests, Len(SearchBox.Text) = 0 || SearchBox.Text in HM.DisplayName || SearchBox.Text in DL.DisplayName || SearchBox.Text in FirstName || SearchBox.Text in Title || SearchBox.Text in 'Id' || SearchBox.Text in 'Created By'.DisplayName ), varsortcolumn, varsortdirection ) )

However, I need to take a moment to explain exactly what the code you posted is doing. A With() statement is a (very) temporary Table Variable and can be dealt with like a Collection, however the output (the list filtered can be of any size) is also limited to 2,000 records. The bottom filter is then dealt with "locally" as the in function (like Search) is not Delegable (if added to the main filter, it would make the entire filter non-delegable).

So essentially, if the user adds Filter elements from the three drop-downs that result in an output of under 2,000 records, then the bottom filter will act on these perfectly and you will get the expected result. If however the top filter produces more than 2,000 records, the bottom filter will only be presented with the first 2,000 records (newest by your Sort) to then Filter on. Also Search would probably be more efficient in the bottom filter.
SortByColumns( Search( _activerequests, SearchBox.Text, HM.DisplayName, DL.DisplayName, FirstName, Title, 'Id', 'Created By'.DisplayName ), varsortcolumn, varsortdirection )

I am assuming here that Id is a Text field (otherwise leave it out).

Other than using StartsWith instead of in (or Search as above which is also not Delegable), there is no way in Power Apps to apply that function directly to 5,000 records. I am not going to get into possible Power Automate solutions here (see this video) which would be very complex with the extent of the filter you are attempting.

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
Suggested answer

oyepanky 541 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Poweruser32490,

The issue is caused by SharePoint delegation.

Your first Filter() is delegable because it uses equality (=) conditions, so SharePoint filters the data on the server.

However, in your second filter you're using the in operator:

SearchBox.Text in HM.DisplayName || SearchBox.Text in DL.DisplayName || SearchBox.Text in FirstName || SearchBox.Text in Title || SearchBox.Text in 'Id' || SearchBox.Text in 'Created By'.DisplayName

The in operator is not delegable for SharePoint. When no filters are selected, Power Apps only searches the first 2,000 records (or your app's configured Data row limit), so records beyond that are never evaluated.

Update your formula

If a Starts With search is acceptable, replace the search portion with StartsWith() (delegable for SharePoint text columns):

With( { _activerequests: Filter( 'Datasource', (IsBlank('Process Type CB'.Selected.Value) || ChoiceProcessType.Value = 'Process Type CB'.Selected.Value) && (IsBlank('Employee Type CB'.Selected.Value) || ChoiceResourceType.Value = 'Employee Type CB'.Selected.Value) && (IsBlank('Region CB'.Selected.Title) || Region_New = 'Region CB'.Selected.Title) ) }, SortByColumns( Filter( _activerequests, IsBlank(SearchBox.Text) || StartsWith(Title, SearchBox.Text) || StartsWith(FirstName, SearchBox.Text) ), varsortcolumn, varsortdirection ) )

Note: StartsWith() is delegable only for text columns. Person fields such as HM.DisplayName, DL.DisplayName, and 'Created By'.DisplayName are still not fully delegable for SharePoint.

If your business requirement is a contains search across all 5,000+ records (not just "starts with"), SharePoint cannot perform that delegably. In that case, you should consider:

Using Dataverse or SQL Server for delegable search.

Using a Power Automate flow to perform the search on the server.

Creating a dedicated searchable text column if that fits your scenario.

I hope this helps! If this resolves your issue, please consider marking it as the Verified Answer so it can help others facing the same problem.

Best regards,
Pankaj Jangid (OyePanky)
📧 Mail: oyepanky@gmail.com
▶️ YouTube: https://www.youtube.com/@oyepanky
🌐 Blogs & More: https://www.dialforit.com

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

Like
a
(0)

Report
Copy link

Link copied!

Hi @Poweruser32490,

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