cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TiagoFreire
Level 8

Re: Pulling in large-ish SQL tables

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. 

Worth a look:
https://powerusers.microsoft.com/t5/General-Discussion/500-item-limit-in-CDM-entity-search-filter-ne...

 

Super User
Super User

Re: Pulling in large-ish SQL tables

@lostgical 

What kind of column is recordID ?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Super User
Super User

Re: Pulling in large-ish SQL tables

Hello everyone.

 

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:
https://youtu.be/DII10gK715I

 

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:
https://youtu.be/BAGBzI4zdww

 

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.

Eelman
Level 10

Re: Pulling in large-ish SQL tables

@RandyHayes 

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 🙂

 

Cheers

Super User
Super User

Re: Pulling in large-ish SQL tables

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.

Eelman
Level 10

Re: Pulling in large-ish SQL tables

@ericonline 

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.  

Helpful resources

Announcements
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (3,816)