Nice trickery...but there is no way to execute a formula by text. In other words, you can't execute that collectCommand in that way.
But I have to rewind back to the - why so many records needed for a PowerApp??
Especially since you are working with SQL where you can write a view or function to filter down to the minimum. I kind of saw most of these tricks we discussed in this post as a means to tame SharePoint and other sources where you have no ability to create a view or function, and still needed to be able to search or find specific records beyond the limits...not necessarily because we wanted to have thousands of records in the PowerApp, but only because there was no other way to delegate a query to the source. But, with SQL you can.
Is that not an option for you in this?
I also agree that a view could be a great solution as it could remedy the need to monitor the table. Is this for an offline app or something like that? Maybe if you could elaborate on the data scenario a bit we could all collaborate on a solid solution.
Always love hearing creative ideas to solve problems.
RE: Why pull in so much data to PowerApps?
I still incorporate SQL Views and DropColumns to pare down to a "base" mass dataset, but 20-30k records plays pretty nice when saved locally 🙂
As Mike8 mentioned earlier you can use flow to do this without all the convoluted logic. Flow executes the query for you --with no row limit-- and passes the array of records back to powerapps.
@ericonline for the piece below inserted into OnStart, I'm getting Delegation warning.
Concurrent( ClearCollect(col1, Filter(sharepointList, recordID >= 1 && recordID <= 2000)), ClearCollect(col2, Filter(sharepointList, recordID >= 2001 && recordID <= 4000)), ClearCollect(col3, Filter(sharepointList, recordID >= 4001 && recordID <= 6000)) ); ClearCollect(colCombined,col1,col2,col3)
Any ideas on how to approach this? The app's data row limit is set to 2000.
Read the announcement for more information!
Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.
Features releasing from October 2019 through March 2020
Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications