cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Collection using SQL table

I'm trying to put a SQL table into a collection.  I have a table called 'Oversight' and I'd like to place it into a collection called 'Otbl'  I've tried putting the following into the OnVisible property of the screen.

 

ClearCollect(Otbl,'[dbo].[Oversight]')

 

When I do this only the field headers are returned, no records.  I've tried using a button to refresh the data source, tried using just the Collect function, watched all of Shane Young's videos, and combed over MSFT documentation and I'm just stuck.  Any help would be very, very appreciated.

 

Thanks,

-Andrew

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Hi @AMontgomery ,

Have you define a proper Primary Key in your SQL Table?

Have you fire the OnVisible property of screen properly?

 

Firstly, please make sure you have defined a proper Primary Key in your 'Oversight' SQL Table. In addition, please make sure the OnVisible property of the screen has been fired properly. The OnVisible property of a screen would be fired only when you navigate back to current screen from other screen.

On your side, you could go to other screen, then navigate back to current screen holds the ClearCollect() formula, then check if the Otbl collection populated with proper records.

 

You could consider add a button in your app screen, set the OnStart property to following:

ClearCollect(Otbl,'[dbo].[Oversight]')

press the button, then check if the Otbl collection is populated with proper records. If yes, it means that the OnVisble property of your screen has not been fired before.

 

Actually, you could consider put your ClearCollect formula into the OnStart property of App, each time you run your app, the OnStart property would be fired. Set the OnStart property of App to following:

ClearCollect(Otbl,'[dbo].[Oversight]')

then re-load your app, then check if the Otbl collection is populated with proper records.

 

Also please consider turn off the "Explicit column selection" option within Advanced settings of App settings of your app, save and re-load your app.

5.JPG

 

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.

View solution in original post

2 REPLIES 2
Community Support
Community Support

Hi @AMontgomery ,

Have you define a proper Primary Key in your SQL Table?

Have you fire the OnVisible property of screen properly?

 

Firstly, please make sure you have defined a proper Primary Key in your 'Oversight' SQL Table. In addition, please make sure the OnVisible property of the screen has been fired properly. The OnVisible property of a screen would be fired only when you navigate back to current screen from other screen.

On your side, you could go to other screen, then navigate back to current screen holds the ClearCollect() formula, then check if the Otbl collection populated with proper records.

 

You could consider add a button in your app screen, set the OnStart property to following:

ClearCollect(Otbl,'[dbo].[Oversight]')

press the button, then check if the Otbl collection is populated with proper records. If yes, it means that the OnVisble property of your screen has not been fired before.

 

Actually, you could consider put your ClearCollect formula into the OnStart property of App, each time you run your app, the OnStart property would be fired. Set the OnStart property of App to following:

ClearCollect(Otbl,'[dbo].[Oversight]')

then re-load your app, then check if the Otbl collection is populated with proper records.

 

Also please consider turn off the "Explicit column selection" option within Advanced settings of App settings of your app, save and re-load your app.

5.JPG

 

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.

View solution in original post

Kris,

My primary key was set in SQL and I'm firing the onvisible property correctly.  I tried using a button to initiate everything and I kept getting the same results, no records only headers.  I worked for at least 3 hours on this issue at which point I saved and exited.  When I came back records were populated.  🤔  I honestly think PowerApps was just being buggy and I didn't have the experience to realize sometimes you just need to 'reboot' powerapps when you're getting odd behavior.  Thanks for the response, hard lesson learned.

Helpful resources

Announcements
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (11,557)