Hi, I understand the row limit when fetching items usingh the sql connector us 500 items (I can up it to 2000). If a call a flow to fetch the items from the list, can oi return more rows?
The 500 (up to 2000) Items limite is the delegation limit. That means that if your formula on the SQL table can not be deleted, it will operates on the 500 first items. But you have a lot of delegations options with SQL, meaning that you are able to perform a lot of operation against your SQL table with no items lilit, because PowerApps is able to delegate your queries to SQL itself.
Maybe you can tell us a bit more about your scenario so we can see how you can work with the delegation ?
To answer your question you have also limits when using Flow to retrieve big dataset, especially the 90secondes timeout limit : if your flow takes more than 90 secondes tu respond to PowerApps, then the http response failed and nothing is retrieved to the app.
I'm still looking for a way to upgrade that limit.
Using a getitems in Flow to get more than 2000 items will probably take more than 90secondes, and PowerApps waiting for flow response will timeout.
You can probably use a SQL view, but I haven't work with so can't really tell you how it behaves with large data set.
Do you want to display them in a Gallery ?
How do you filter these more than 2000 items ?
You don't have to store them locally in your PowerApps, it will affect your performances. But, and this is the power of delegation, you can let your users go all over them and ask SQL to provide data when the users requires.
Tell us more about the type of data (text/number/dates) and what you want to achieve.
Using a getitems in Flow to get more than 2000 items in my flow takes just milliseconds.
I was planning on displaying the items in a gallery, and yes, users would need to scroll thru all 2000 of them!(its an audit thing... user needs to confirm he approves all 2000 rows. )
I switched to an spfx webpart that calls the flow to avoid any potential delegation issues., so i have it working now.
Still would like to know if i can circumvent row limit by using Flow though.
Well I don't know about Flow retrieving 2000items to PA. Try it by using a Request Response action in your Flow.
But you can also directly plug your SQL table to a gallery, you'll be able to scroll over 50000 items if you want to, thanks to delegation.
I agree on some points with tchin-nin but my opinion is that flow is a good solution to circumvent the row limit.
Here is a video that 30000 records are being retrieved in 4 seconds.
As tchin-in said,
"You don't have to store them locally in your PowerApps, it will affect your performances. But, and this is the power of delegation, you can let your users go all over them and ask SQL to provide data when the users requires."
depending on your phone or tablet that you are using this may be an issue.
However if you need for example to filter, having the data locally stored, has better perfomance.
I recommend you to try it by yourself, RusselGove and decide what suits you.
You can use this as a reference:
Keep your eyes open for our upcoming T-shirt design contest!
Fill out a quick form to claim your user group badge now!
Find out where you can attend!
Features releasing from October 2019 through March 2020