ClearCollect() of an sql table not getting all data columns

[martinav]

I'm doing a very simple collection of some rows of a sql table.  Nothing special at all, just a simple filter.  I only get a couple of columns.  I have pulled this statement into a button by itself.  Where's all my data?  I did a remove/add back of the connection.  Nope.  Same crap.  Every column should have something in it.  No matter the number of results, same columns are represented, same ones are empty.

[RandyHayes · Accepted solution]

@martinav 

This is an issue with Collections as they are static snapshots of data at a given time.  

Turn the Explicit Column Selection feature off in your app settings.  

PowerApps is designed to be lazy-loading - gets data when required.  For a collection off of a filter, it will only give you the columns that are "demanded" in the filter statement.  All others are ignored.

The feature mentioned will turn off this performance boost and put your app back into a mode where it constantly takes the performance hit of getting all data all the time.

I hope this is helpful for you.

[martinav]

@RandyHayes 

I have been doing this a very long time.  Been using collections for years now.  I have not had this happen before.  What has changed here?

[RandyHayes]

@martinav 

Nothing has changed.  This is the default behavior that you are seeing when working with SQL as it is a datasource that will honor that feature setting.

Not sure why you have not encountered it before, but it has always been there.

[martinav]

@RandyHayes ,

Ok, I see.  If I use a column in another statement somewhere, it takes that into account, and includes the column.  Its not just the filter statement itself.  Thats why I never noticed.  If I call for the data, it puts that column in.  I'm good with that.

Thanks!

[RandyHayes]

@martinav 

Not really in the sense of a collection.

If you issue a Collect(collection, Filter(source, field1 = x && field 2 = y))

Then your collection will have all the rows, but will only have values in field1 and field2, as those were "demanded" in the formula.

If you then later use the collection and try to utilize a column in that collection that was not filled in to begin with - it will still remain empty because the collection is a snapshot static data table based on what it collected in the first place.

However...if you use your datasource directly and not with in-memory database tables (collections), then it will observe the feature and only gather columns demanded - and thus, increase app performance.

So, if you had a gallery with an Items property of yourDataSource

And in it you had a label with a text of ThisItem.field1, then it will technically only pull down field1 values and not all columns.  If you later "demanded" other fields, then it will fill in the columns as demanded.  All dynamically.

NOTE: this also brings up a little known aspect of PowerApps in regard to getting data from the datasource.  If you first get only field1 from a record with the feature on, and then someone changes field2 in the record, and then later you "demand" field 2 in the app...what will the value be for field2?  The one that existed when field1 was gathered or the new one?  The answer is...the new one!  And then, that is it.  If you still reference that record in your app and then someone changes field2, your app session table (the cloud table) has already loaded that field2 value and so it will return it as it last got it.  

There is more to the above story, but that is the basic part as it applies to the feature mentioned.

[martinav]

@RandyHayes ,

Your in it way deeper than I am, but I did test it.  I used a field in another object, and the clearcollect() pulled that field for all rows after I added it.  It has to work that way, or so many of my other apps would not be working.  I never changed this off of default before.  But, leave it to PA to have things work not as advertised, if its advertised at all.  Or... to change how it works and not tell us.  We are all in it together to figure out how this thing works.

[RandyHayes]

@martinav 

Yes, once you demand any field in your app, from that point forward it is available to other functions and formulas.

But the feature mentioned is specifically for those that are not duplicating their data in the app into a collection and are working directly with the data.  It will boost performance quite a bit.  If you need to copy all of your data into your app memory for some reason, that that feature should be turned off.

Also...keep in mind, it is datasource specific.  SQL honors that feature, SharePoint does not.  So, SQL apps should be much more performant with that feature in use.