How to import more than 2000 rows from my SQL table dynamically?

[MarioJohnston]

Hello All,

I am encountering an issue with an app of mine that I am currently in the process of reworking due to delegation issues. I am trying to accomplish the following.  I have a SQL table named ROUTEORDERS that currently has 15,000 or so records. This table will have new rows added daily, approx 3500 or so. I need to only gather the latest 10,000 records and combine them into one collection that the app can use to filter from. Example Today has 15,000 records. I need it to pull the latest records decending, 15,000-13001, 13000-11001, 11000-90001, and so forth.

I have been able to write the following code but when I check my collections col4000Records pulls the same data as col2000Records. 

ClearCollect(colSQLImport,
SortByColumns(ROUTEORDERS,"ID",SortOrder.Descending));


//Fetches the latest 2000 records from ROUTEORDERS
ClearCollect(col2000Records,FirstN(SortByColumns(ROUTEORDERS,"ID",SortOrder.Descending),2000));

//Fetches the next 2000 records (2001-4000) from ROUTEORDERS
ClearCollect(col4000Records,FirstN(Filter(SortByColumns(ROUTEORDERS,"ID",SortOrder.Descending),ID > Last(col2000Records).ID),2000));


//ClearCollect(colAllOrdersCombined,colLast2000Records,colLast4000Records); 
ClearCollect(colAllOrdersCombined,col2000Records,col4000Records);

How can I adjust this so that it will continue to pull the next set of records? 

[TheRobRush]

Your app is unlikely to function very well with that many records in it at the same time. Is there a reason you need to see all 15,000 at the same time? Might want to move the functionality to PowerAutomate. Can also check here if you haven't yet, and here as well.

[MarioJohnston]

Thank you for those examples, I will review those links and see if I am able to produce any positive results.

To answer your question, I was giving that example, at most, we would need 10,000 records to filter from. We would need this so that we could pull orders using our app. Due to the delegation issue, the 2,000 limit is causing issues, and cannot see orders in our app. Or it will be partial due to the next incoming rows.

[TheRobRush]

What filter are you using that cannot be delegated?

[MarioJohnston · Accepted solution]

Thank you for the reply.  I was trying to filter for a date. However, my team and I were able to go back to the drawing board and wrote code in a different direction that would not load all the records.