Unrolled loops of ClearCollects would not scale as data grows.
There is this little piece of dark magic that should work, though I have not tried it first hand.
It was created in the context of the user's CDM, but should work on Canvas apps I believe.
Nice thing is, it should work without preconceived knowledge of what's your maximum number of rows.
What kind of column is recordID ?
We have also used the ClearCollect method in a ForAll loop, to batch import the records from SQL in pages of 2000. Pulling the data from a view helps a lot, but there are some instances where nothing beats using SQL queries or stored procedures.
Below are two videos dealing with advanced queries to SQL from PowerApps (using Flow).
For SQL Azure you can use direct (native) queries:
For on-premesis SQL servers, native queries are not supported from Flow (you will get an error: operation Execute Native Sql is currently not supported using an on-prem gateway connection), so we have to use stored procedures to accomplish the same:
Please let me know if you don't come right.
Dawid van Heerden
Follow on Twitter: @davestechtips
Subscribe to YouTube: https://www.youtube.com/davestechtips?sub_confirmation=1
**If you found this reply helpful, please mark this as the answer to close the topic and make it easier to find for other people with similar questions.
This is great mate! Wish I'd found this earlier in the year. I need large SharePoint dataset filter options and I should be able to work with this process - unless you have developed something better since then 🙂
Hi @Eelman ,
I've been using Flow to query large Sharepoint lists recently. There is an action called Send an HTTP Request to Sharepoint that will allow you to interact with the API. It can be temperamental, but I've successfully used it on lists > 20k records and views > 5k records.
Holler if you want to chat further.
Thanks mate. I just had a read over the docs for Send an HTTP Request to Sharepoint and it's a little above my understanding, currently. I would love to see how you use this flow, if you are able to show me, because I'm all for learning something new.
Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.
Fill out a quick form to claim your user group badge now!
Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities
Features releasing from October 2019 through March 2020
Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.
Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications