Frustrated with Delegation

[astrontelstar]

I have a PowerApp with my db table in Dataverse.

I am approaching the 2000 limit (currently 1950) of purchase orders in my db table.

My users need to be able to search the entire database for all the orders of any given client and I display these PO's in a gallery for them to interact with.  I've been testing what happens if I have more than 2000 with my search and it appears I am going to have an issue with the search tool not returning some PO's from clients on anything over the first 2000. 

I built a test table with 3000 records of name, ponumber and have tried searching all records that just contained "PO" as the first part of the text to see if I could build a collection for a gallery that showed all 3000 records and it stops at 2000 every time which tells me my logic isn't delegable.

What are people doing that have larger numbers of records?  I can't fathom any good business that wouldn't need to run a query to search thousands of records, not limited to 500 or 2000.  I can't seem to get a logic that is delegable on this recordset to work to let me have my collection of all 3000 records (or any number I wish).  

[mandela]

One possible solution is to use the StartsWith function, which is delegable for Dataverse. This function returns true if a text value begins with another text value. For example, you can use this formula in your gallery’s Items property:

Filter('Purchase Orders', StartsWith(ponumber, "PO"))

This formula will filter the purchase orders table by the ponumber column, and only return the records that start with “PO”. This will delegate the query to the data source, and bypass the delegation limit.

However, this solution may not return all the records that contain “PO” in the ponumber column, such as “123PO456”.

[Pstork1]

Search is a delegable function for Dataverse. So it should work for text columns.   Are you sure search has been enabled for the columns you are trying to search on? That is a setting in the schema of the Dataverse table.  I have a Dataverse table with 5,000+ records and both Search and Filter work fine on columns like Name.  Check the datatype of the columns and make sure Search is enabled in the settings for the columns.

[astrontelstar]

Thank you for the quick response.  I did NOT have searchable selected in the dataverse test table.  I went in, found the PO column and turned on the search.
I then went back to the powerapp, refreshed the table and reran the following:

//Set(myBigCollection, Search(TestDelegationTables,"PO","cr014_ponumber"))

Set(myBigCollection, Filter(TestDelegationTables,"PO" in PONumber))

I tried both search and filter and my count 

"Collection:" & CountRows(myBigCollection) still shows 2000, rather than the 3000 in the db.

I feel I'm missing something simple.  Appreciate anything.

[Pstork1]

Is PONumber a multiselect column?  That would be why you would use in.  If you just want to match the first few letters of a PONumber in a text field then you use either startswith or 

'='

[astrontelstar]

Here is my column setting.  Just a text column with search turned on.
I need to be able to have the program return anything I search for in this field so I get ALL the PO's for any given client or search criteria.  

[astrontelstar]

I also tried this and it doesn't work either.  

ClearCollect(myBigCollection, Filter(TestDelegationTables, StartsWith(PONumber, "PO")));
Still shows only 2000 records.

[Pstork1]

Let me clarify what I think is a misconception. Power Apps has a data row limit. It will never return more than a maximum of 2,000 records on a single request. Delegation means that I can return up to 2,000 records out of a much larger number of records. So if I have a data source with 10,000 records and I search or filter on something that matches 1,000 of those records I will get all 1,000 records no matter where they are in the 10,000. If I use a non-delegable function then I will get whatever records out of the first 2,000 match the criteria. But I won't get any records above 2,000. So whether you use a delegable or non-delegable function your collection would never have more than 2,000 records. To get more than that you need to run multiple Collects with different criteria. For example if you were to filter and retrieve all the records where title starts with "A" and then filter all the records that start with "B" then your collection will have all the records that start with A or B.  That collection could be up to 4,000 records if you had 2,000 A and 2,000 B records.  But doing it that way isn't a good design.

Instead what you should do is try to provide your users with tools like Search and Filter that can focus in on the records they need to work with instead of trying to get everything.

[astrontelstar]

Hmmm.  Ok.   So if I want to find say a certain PO out of thousands, I can get up to 2000, but that's it.  If other's match the criteria, then I'm out of luck.  So if I want to pull all orders for a client, but they have over 2000 orders (say they make 2000 orders a year) I can only retrieve one years data I'm out of luck.  The other use case: If I want to iterate over each order they have in a for all loop, I can't really do that unless I use Power Automate because of the 2000 limit?  Just trying to make sure I understand.   

[ivan_apps]

I would break your requirements into different asks -

1. I want to pull all orders for a client for the past year for reporting purposes. —> use Power BI, integrate it with the app as a tab and done. You can search and filter all you want and create great visualizations. Alternative - use a model-driven app. MDA views can work easier and return all data in 5000 chunks, but you can page through it all.

2. I want the user to search for a specific order in the past year (2000+ orders). —> if you are running into a 2k limit on search, I would arguably say your search criteria isn’t good enough. Who wants to page through 2k records to find 1? Add a time range and warn the user if 2k is returned, saying they should refine criteria. If you are constantly running into delegation warning I would consider migrating to a MDA and creating custom pages for your UI. Leave the search to the native MDA capabilities as they are more powerful.

3. Iterating over 2k items in a forall loop. —> Canvas apps are good at certain things but just like any application outside the power platform, you need to send long running processes to an asynchronous workflow engine. Why would you want your users to wait while you make 2k+ requests and process them while they’re locked in the screen? What if they close the browser? It’s not transactional so your update is incomplete. Send these large updates to Power Automate is best practice in general, not a downside of delegation. Build in error handling and notifications as you see fit, and you can easily email the results back out to anyone who needs it.