cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User III
Super User III

Re: Pulling in large-ish SQL tables

@tianaranjo 

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?

_____________________________________________________________________________________
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.
Highlighted
Super User
Super User

Re: Pulling in large-ish SQL tables

@tianaranjoThat is a very clever approach! As @RandyHayes mentioned, formula from text doesn't fly unfortunately (for which I can see pros and cons). 

 

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.

Highlighted
Skilled Sharer
Skilled Sharer

Re: Pulling in large-ish SQL tables

@RandyHayes @wyotim
Well ~ I technically won’t be needing to pull in the tables (for this particular app) However, I had the idea 💡 and decided to go down the rabbit hole.l either the idea that this could be a solution for large SharePoint lists later (where sql is not an option). Thanks for the replies!
Seemed like a good possibility.
Highlighted
Super User
Super User

Re: Pulling in large-ish SQL tables

Indeed! Well, I am always down for hypotheticals and theoreticals. It’s fun to see how far something can be taken.
Highlighted
Super User III
Super User III

Re: Pulling in large-ish SQL tables

Always love hearing creative ideas to solve problems. Smiley Happy

_____________________________________________________________________________________
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.
Highlighted
Skilled Sharer
Skilled Sharer

Re: Pulling in large-ish SQL tables

What?¿ No duct tape for this ~ I thought we could duct tape almost anything in PowerApps.
Highlighted
Super User
Super User

Re: Pulling in large-ish SQL tables

@RandyHayes and @wyotim :

RE: Why pull in so much data to PowerApps?

  • For me, the biggest pulls for experimenting with mass data are:
  • This (in my mind) has the potential for near-offline, lightning fast apps with only incremental updated of the large dataset AFTER an initial (potentially "long") sync. 
  • After watching Paul O'Flaherty's most recent video, he's got me sold on trying this out!

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 🙂

 

Highlighted
Kudo Kingpin
Kudo Kingpin

Re: Pulling in large-ish SQL tables

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.

Highlighted
Advocate II
Advocate II

Re: Pulling in large-ish SQL tables

@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. 

 

Highlighted
Super User
Super User

Re: Pulling in large-ish SQL tables

Hi @lostgical . Hm. Looking at the docs for Sharepoint and delegation, Sharepoint can be picky (no DateTime or Complex fields). You'll have to make sure your Sharepoint List fits all the criteria.

 

Thats why I went with SQL for this particular example. 

 

Good luck!

image.png

 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (2,841)