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

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

KickingApps
Level 8

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

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.
KickingApps
Level 8

Re: Pulling in large-ish SQL tables

What?¿ No duct tape for this ~ I thought we could duct tape almost anything in PowerApps.
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 🙂

 

RussellGove
Level 10

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.

lostgical
Level: Powered On

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. 

 

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
New Ranks and Rank Icons in April

'New Ranks and Rank Icons in April

Read the announcement for more information!

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.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

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 (9,562)