cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Trung
Level: Powered On

How to load data from a Table (in Azure SQL) to Collection?

Hi guys!

 

I want to load all record from a Table ('[dbo].[AAATbl]') into a collection (CollectionAAA).

Below code created a AAA collection but without any data (AAA just has Column names, no record).

 

Collect(AAA; '[dbo].[AAATbl]')

 

Thanks in advanced!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: How to load data from a Table (in Azure SQL) to Collection?

Hi @Trung 

 

The expression is correct. This just needs to be triggered by some mechanism. You can put this expression on the "OnSelect" property of the button and once the app is played and the button is clicked, this will fill the data into your collection.
 
Button -> OnSelect -> Collect(AAA; '[dbo].[AAATbl]')

 

Hope this Helps

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Community Support Team
Community Support Team

Re: How to load data from a Table (in Azure SQL) to Collection?

Hi @Trung ,

Where do you put your Collect formula? Within OnStart property of a button?

Do you want to load all records from your Azure SQL Table into a collection?

How many records existed in your Azure SQL Table? More than 2000?

 

Firstly, if you want to load records from your Azure SQL Table into a Collection, you need to put your Collect formula within a behaviour property of a control, e.g. OnStart property of a Button.

 

Please consider add a button within your app, set the OnSelect property to following:

Collect(AAA; '[dbo].[AAATbl]')

then press the button, check if the AAA collection is populated with proper records.

 

In addition, currently, the Collection within PowerApps could only retrieve 2000 records at most from other cloud service once time. If the amount of your Azure SQL Table records is more than 2000, it could only retrieve first 2000 records into your collection at most.

Note: Please set the "Data rows limits for Non-delegable queries" option within Advanced settings of App settings to maximum value -- 2000.

 

If the amount of your Azure SQL Table records is more than 2000, you could consider bulk-load data from your Azure SQL Table into multiple individual collection, then merge these collections into single one collection, then use the merged collection as data source in your app.

Please check and see if the alternative solution within the following thread would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M7...

Note: I assume that you have already added a Identity(1,1) type column in your Azure SQL Table as Row Index. e.g. 1,2,3,4,.....

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3

Re: How to load data from a Table (in Azure SQL) to Collection?

Hi

 

Can you see the data from the table within a gallery ?

When do you trigger the collect ? (on app starts or from a button ?)

 

Regards

Super User
Super User

Re: How to load data from a Table (in Azure SQL) to Collection?

Hi @Trung 

 

The expression is correct. This just needs to be triggered by some mechanism. You can put this expression on the "OnSelect" property of the button and once the app is played and the button is clicked, this will fill the data into your collection.
 
Button -> OnSelect -> Collect(AAA; '[dbo].[AAATbl]')

 

Hope this Helps

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Community Support Team
Community Support Team

Re: How to load data from a Table (in Azure SQL) to Collection?

Hi @Trung ,

Where do you put your Collect formula? Within OnStart property of a button?

Do you want to load all records from your Azure SQL Table into a collection?

How many records existed in your Azure SQL Table? More than 2000?

 

Firstly, if you want to load records from your Azure SQL Table into a Collection, you need to put your Collect formula within a behaviour property of a control, e.g. OnStart property of a Button.

 

Please consider add a button within your app, set the OnSelect property to following:

Collect(AAA; '[dbo].[AAATbl]')

then press the button, check if the AAA collection is populated with proper records.

 

In addition, currently, the Collection within PowerApps could only retrieve 2000 records at most from other cloud service once time. If the amount of your Azure SQL Table records is more than 2000, it could only retrieve first 2000 records into your collection at most.

Note: Please set the "Data rows limits for Non-delegable queries" option within Advanced settings of App settings to maximum value -- 2000.

 

If the amount of your Azure SQL Table records is more than 2000, you could consider bulk-load data from your Azure SQL Table into multiple individual collection, then merge these collections into single one collection, then use the merged collection as data source in your app.

Please check and see if the alternative solution within the following thread would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M7...

Note: I assume that you have already added a Identity(1,1) type column in your Azure SQL Table as Row Index. e.g. 1,2,3,4,.....

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 239 members 4,628 guests
Please welcome our newest community members: