cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AMontgomery
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
v-xida-msft
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
v-xida-msft
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.

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
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (1,649)