cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ericonline
Community Champion
Community Champion

Pulling in large-ish SQL tables

Hello,

Is this how everyone is pulling in large-ish SQL data sets (~35k records)? Or do you use another method?

  1. Set Advanced Settings max to `2000`
  2.  
Concurrent(
    ClearCollect(col1, Filter('[dbo].[bigSqltable]', recordID >= 1 && recordID <= 2000)),
    ClearCollect(col2, Filter('[dbo].[bigSqltable]', recordID >= 2001 && recordID <= 4000)),
    ClearCollect(col3, Filter('[dbo].[bigSqltable]', recordID >= 4001 && recordID <= 6000)),
    ClearCollect(col4, Filter('[dbo].[bigSqltable]', recordID >= 6001 && recordID <= 8000)),
    ClearCollect(col5, Filter('[dbo].[bigSqltable]', recordID >= 8001 && recordID <= 10000)),
    ClearCollect(col6, Filter('[dbo].[bigSqltable]', recordID >= 10001 && recordID <= 12000)),
    ClearCollect(col7, Filter('[dbo].[bigSqltable]', recordID >= 12001 && recordID <= 14000)),
    ClearCollect(col8, Filter('[dbo].[bigSqltable]', recordID >= 14001 && recordID <= 16000)),
    ClearCollect(col9, Filter('[dbo].[bigSqltable]', recordID >= 16001 && recordID <= 18000)),
    ClearCollect(col10, Filter('[dbo].[bigSqltable]', recordID >= 18001 && recordID <= 20000)),
    ClearCollect(col11, Filter('[dbo].[bigSqltable]', recordID >= 20001 && recordID <= 22000)),
    ClearCollect(col12, Filter('[dbo].[bigSqltable]', recordID >= 22001 && recordID <= 24000)),
    ClearCollect(col13, Filter('[dbo].[bigSqltable]', recordID >= 24001 && recordID <= 26000)),
    ClearCollect(col14, Filter('[dbo].[bigSqltable]', recordID >= 26001 && recordID <= 28000)),
    ClearCollect(col15, Filter('[dbo].[bigSqltable]', recordID >= 28001 && recordID <= 30000)),
    ClearCollect(col16, Filter('[dbo].[bigSqltable]', recordID >= 30001 && recordID <= 32000)),
    ClearCollect(col17, Filter('[dbo].[bigSqltable]', recordID >= 32001 && recordID <= 34000)),
    ClearCollect(col18, Filter('[dbo].[bigSqltable]', recordID >= 34001 && recordID <= 35000))
);
ClearCollect(colCombined, 
    col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18
)
39 REPLIES 39

@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 below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
wyotim
Resident Rockstar
Resident Rockstar

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

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

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

Always love hearing creative ideas to solve problems. Smiley Happy

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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

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

 

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.

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

 

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,382)