Answered

Collection not collecting all columns

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi Everyone!

I've been having an annoying issue for quite some while now. I am working with collections of rather small SQL views, in order to load data faster in my PowerApp. I have tried to use my ClearCollect function both OnStart and OnVisible of the front page. But for some reason, my collection is not collecting all the columns, it is simply ignore specific columns every time.

The view that I am having most issues with, is an SQL view that has roughly 30 - 60 rows, and 10 columns; so very little data. It manages to collect all rows of 4 of the columns every time; two integer columns, one string column, and one float column. But the rest are just empty, and I need some of that data. I have tried several different things, but it simply isn't working.

Just to make it clear, I have tried the following function in both the OnStart and OnVisible parameter:

ClearCollect(CollectionName,'[dbo].[SQLViewName])

I have also tried to specify each of the columns individually:

ClearCollect(CollectionName, ShowColumns('[dbo].[SQLViewName],"Column1","Column2"... etc)

I hope someone is able to help with this!

Thanks in advance!

Categories:

Building Power Apps

All responses (9)

Answers (1)

Sort by

Verified answer

v-siky-msft Microsoft Employee on at

Like
a
(14)

Report
Copy link

Link copied!

Hi @Anonymous ,

Could you try to turn off "Explicit column selection" preview feature and try again?
Also try to add Refresh('[dbo].[SQLViewName]') before the clearcollect function.
Best regards,
Sik
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-siky-msft

Thank you very much for your response. This was exactly the right solution to my problem.

I'm not sure why this feature has automatically been turned on? It hasn't always been like this. It worked fine for several months prior to last week.

Anyhow, thanks again!

Best regards,
DropDownHelp.
German_Chris 42 on at

Like
a
(1)

Report
Copy link

Link copied!

Many thanks! for this Info.
I spend a lot of time, because i had the same problem!
dave_parth 16 on at

Like
a
(1)

Report
Copy link

Link copied!
After 2 years, I guess the issue is still fairly common.
I did all the steps which were mentioned previously i.e,
Refresh database before collecting data
Turn off Preview feature of 'Explicit column selection'
However, there's additional step you might want to consider.
- Open the app in incognito mode and see if the issue persists.
- CLEAR CACHE from your browser and then check again.

I did the above and it worked for me.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

i have the same problem, I did everything that @dave_parth did and it did not work for me. Is there anything that I can do? (I am trying to collect a sharepointlist with 5 collums and approx. 20 rows and only 3 collums show up)
dave_parth 16 on at

Like
a
(0)

Report
Copy link

Link copied!

Is your @Anonymous sharepoint list still open in the web? Can you please try closing the sharepoint list and refresing the power app again?
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

had to restart the computer in between so i definitly did that. But I think that it just a problem that you can not collect the attachments collum, to a collection (at least if you have more than one document in it). But thanks for your reply.
dave_parth 16 on at

Like
a
(0)

Report
Copy link

Link copied!

Are you writing back in your SQL views using Power App or referring it to populate your power app? If you are not modifying your SQL view then you can consider creating a Virtual table in dataverse. That should be faster.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for the reply but, I am not refering to a SQL datasource I am working with a sharepointlist and I have solved the problem.