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 8

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

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 204 members 5,268 guests
Please welcome our newest community members: