How to expand my search filter past 2000

[ddow2788]

My data has exceeded the 2000 item limit for the search. This is the current formula I am using for my table:

Search('Person List','Search Client'.Text,'Last Name','First Name')

What formula should I use to exceed the 2000 item limit?

[rzuber]

I see nothing in the documentation about a 2000 record limit for Search().. Are you referring to the 2000 record limit for pulling from a data source that doesn't support delegation? (i.e. SharePoint?)

[ddow2788]

yes

[venturemavenwil]

@rzuber actually I believe SharePoint is delegable, just not with the search function

https://learn.microsoft.com/en-us/connectors/sharepointonline/#power-apps-delegable-functions-and-op...

@ddow2788 For your expression, you would probably have to use Dataverse

https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/connections/connection-common-data-se...

Cheers,

[ddow2788]

Do you have any recommended formulas to try?

[rzuber]

I would suggest you collect your people into a collection, but you're going to have to group them together in filters that will be less than 2000 records.. You could start with grouping them by last name.. and work out something more permanent later.. Here is an example I came up with from your provided Search function.

ClearCollect(
    colPeople,
    Ungroup(
        ForAll(
            Distinct('Person List', 'Last Name') As _last,
            Filter('Person List', 'Last Name' = _last.Value)
        ),
        Value
    )
)

[EDIT] If you try this, I am curious how it turns out.

[rzuber]

@venturemavenwil SharePoint is one of the data sources affected by the row limit. If you use Filter() on a SharePoint list, you can't retrieve more than 2000 records at a time. 500 by default, and I usually leave it @500 (except for special situations.. mostly testing) and just pull the max records at a time.

[venturemavenwil]

You could also try to emulate the search function with Filter and StartsWith, both are delegable in SharePoint

so in your collection 

ClearCollect(
    colPeople,
    Filter(
        'Person List',
        StartsWith('First Name', 'Search Client'.Text)
    ),
    Filter(
        'Person List',
        StartsWith('Last Name', 'Search Client'.Text)
    )
)

However, this would only search for records in your columns that start with the text you enter in your text box. 

Please let me know if my answer helped solve your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.

[LaurensM]

@venturemavenwil is correct - SharePoint supports delegation. However, a contains search (e.g. Search function or in operator within a Filter statement) is not delegable for SharePoint.

@ddow2788 

A delegable alternative would be using the StartsWith() function as a Filter condition - this does require the user to write the name correctly. The filter no longer performs a 'contains' query.

Filter(
    'Person List',
    StartsWith('Last Name', 'Search Client'.Text) || StartsWith('First Name', 'Search Client'.Text)
)

In case you want to keep the contains query, another option would be prefiltering your data with a delegable condition. Think of limiting the amount of records between a certain creation date, department, role...

With(
    {
        //Prefilter data via a delegable query (should not return more records than your data row limit)
        wPrefilter: Filter(
            'Person List',
            //Fictive prefilter based on a department dropdown
            Department = cmbDepartment.Selected.Value
        )
    },
    //Filter prefiltered dataset based on a non-delegable query
    Search(
        wPrefilter,
        'Search Client'.Text,
        'Last Name',
        'First Name'
    )
)

I generally recommend the workarounds above as they are quick & easy to implement. That does not mean these are the only options: online you may find other approaches such as fetching all records in memory by creating a collection over 2.000 records and searching the collection (which will affect app performance), using the Graph API showcased by Reza (don't have personal experience with this approach)...

---

If this solves your question, would you be so kind as to accept it as a solution. ✔️

If you liked my solution, please give it a thumbs up. 👍

Connect with me: LinkedIn | Blog

[venturemavenwil]

@rzuber Matthew Devaney actually covered this topic in his blog

https://www.matthewdevaney.com/sharepoint-delegation-cheat-sheet-for-power-apps/

As long as delegable functions are used, the full result is returned