Collection not collecting all columns

[Anonymous]

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!

[v-siky-msft · Accepted solution]

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

[Anonymous]

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]

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

[dave_parth]

After 2 years, I guess the issue is still fairly common.

I did all the steps which were mentioned previously i.e,

1. Refresh database before collecting data
2. 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. 

[broku]

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]

Is your @broku  sharepoint list still open in the web? Can you please try closing the sharepoint list and refresing the power app again?

[broku]

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]

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.

[broku]

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.