Datasource has more than 2000 records. May i know to ensure search/filter works fine on Gallery

(2) Share

Report

Posted on by Poweruser32490

126

I have a datasource SharePoint with more than 2000 records. I need to ensure my gallery filters/search works fine and not run into any delegation issue on the Gallery. May i know how to achieve this? Should i switch to multiple collections and merge them?

Gallery Items

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

Microsoft Dataverse with Power Apps

Experimental features

Error handling

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

Connector development

Governance & administering

Power Apps Pro Dev & ISV

Power Query

General topics

I have the same question (0)

All responses (4)

Answers (0)

Sort by

Suggested answer

11manish 3,333 on at

Like
a
(0)

Report
Copy link

Link copied!
For a SharePoint list with 2,000+ records:

Do not use collections to load all records.

Keep dropdown filters delegable using = comparisons.

Replace all SearchBox.Text in Column conditions with StartsWith() where possible.

Avoid searching Person fields directly if delegation warnings appear.

If true "contains" searching across multiple columns is a business requirement, consider Dataverse or SQL instead of SharePoint.

The biggest improvement you can make to your current formula is eliminating the non-delegable in operators and redesigning the search to use delegable functions such as StartsWith().

Was this reply helpful? Yes No
Suggested answer

WarrenBelz 155,838 Most Valuable Professional on at

Like
a
(2)

Report
Copy link

Link copied!

Hi @Poweruser32490,

The code you have posted is Delegable to the extent that the top Filter inside the With statement will excecute correctly on any sized List without Delegation issues. However the maximum number of records it can return after the filter is 2,000. These records can then be filtered “locally” on the device - a With value is a (very) temporary Variable and is subject in output to your Data Row Limit.

You obviously need to structure your posted query as you have done as the in filter is not Delegable and if added to the main filter, the whole item would fail Delegation.

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

Vish WR 3,748 on at

Like
a
(2)

Report
Copy link

Link copied!

@Poweruser32490

Your formula structure is actually correct. The top Filter inside the With statement is delegable and will work fine against any size list. The issue is the in operator used for SearchBox, which is non-delegable and only runs locally against whatever the top Filter returns, capped at your Data Row Limit.

So if your dropdowns are filtering down the results well below 2000 before the search runs, you will not hit any issues. The problem only shows up when all dropdowns are blank and someone searches the full dataset, because records beyond the limit simply will not be visible.

The simplest fix without rewriting everything is to require at least one dropdown to be selected before the search activates. That keeps the delegable Filter doing the heavy lifting and the local search working on a manageable subset.

If you genuinely need full contains search across all 2000 plus records with no pre-filter, switching to Dataverse is the proper long term solution as SharePoint cannot support that pattern reliably.

Was this reply helpful? Yes No
WarrenBelz 155,838 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