Answered

ShowColumns doesn't work as expected when applied to a SharePoint list (column names still appear in the collection)

(0) Share

Report

Posted on by JeffTex

When I apply ShowColumns while building a collection from a SharePoint table, the undesired columns are still added to the collection. The records in the collection contain no data within these columns. My expectation is these columns do not exist at all in the collection.

See below where I illustrate the result in colCountries. Then see that if I apply the exact same function to build colCountries2 (against a non SharePoint data source that in this example will be an existing collection), ShowColumns works as expected.

ClearCollect(
 colCountries,
 Sort(
 ShowColumns(
 tblCountry,
 "ID", "Country", "Selectable", "ReplacementFK", "PTO"),
 Country
 )
)

Now, I will repeat this same operation on an existing collection:

ClearCollect(
 colCountries2,
 Sort(
 ShowColumns(
 colCountries,
 "ID", "Country", "Selectable", "ReplacementFK", "PTO"),
 Country
 )
)

Categories:

Building Power Apps

All responses (4)

Answers (1)

Sort by

Verified answer

RusselThomas 4,014 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @JeffTex ,
It appears the columns that are being "Added" to your ShowColumns() are empty - something like "Created" would never be empty, so I can only guess that somewhere else in your app you've defined the collection with those columns - can you try perform the same ShowColumns() function but collect it into a new different collection name? eg:
ClearCollect( colCountriesTest, Sort( ShowColumns( tblCountry, "ID", "Country", "Selectable", "ReplacementFK", "PTO"), Country ) )
and check to see whether colCountriesTest also shows columns you didn't include?
If it does, we can perhaps dig a little deeper - if not, then I think you've got a rogue Collect() or ClearCollect() statement somewhere defining those columns for your collection...
Kind regards,
RT
JeffTex 57 on at

Like
a
(0)

Report
Copy link

Link copied!
Thanks for the suggestion. That is exactly what I was thinking as well. I had already gone so far as to using the VERY NICE new search feature to examine everywhere that collection was referenced (finding nothing that alarmed me at all).

Sure enough, just renaming the target collection names in the above yields the expected results. But I do not think the issue is a rogue Collect or ClearCollect.

The collection colCountries is created on launch of the app. Everything downstream makes ref to that collection and nowhere do I refer back to the source table (tblCountry) in reference to collecting. (Except ........... the next paragraph) 🙂

However...... I suspect my issue is in the following OnSelect of a Button. That is where I leverage a temp record using With() during a record patch to subsequently update the collection colCountries. (See how the last UpdateIf() at the bottom of this script uses the temp record 'Change' that was generated during the table patch)

//Capture update patch to use to update collection With( {Target: galCountries.Selected}, With( { Change: Patch( tblCountry, LookUp( tblCountry, CountryPK = galCountries.Selected.CountryPK ), { Selectable: Target.tglSelectableCountry.Value, // //Evaluate which normalized country name was selected. Remember this dd is only visible if the toggle was set to not selectable. When the toggle is set to selectable, //the (now hidden) drop down value is defaultly set to the item's country. ReplacementFK: With( {Selection: Target.ddReplacementCountry.Selected}, //If the choice of none was selected, the replacement FK will equal this country PK. That in combination to being not selectable means this country is not available //as a selection to anyone. If( //the country is selectable, then the replacementFK is itself Target.tglSelectableCountry.Value, Target.CountryPK, //non selectable but no replacement is chosen Selection.Country = "None", Target.CountryPK, //Else default Selection.CountryPK ) ), PTO: Value(Target.txtCountriesPTO.Text) } ) //Patch ends here // }, //Temp record 'Change' created // //Update the collections //Collection used for this screen UpdateIf( colManageCountries, CountryPK = Change.CountryPK, Change ); //Main country collection used when building out campaigns UpdateIf( colCountries, CountryPK = Change.CountryPK, Change ) ) )

Even though this OnSelect has yet to execute on AppLaunch, my hypothesis is PowerApps (like other platforms), runs through the defined "functions" established. Seeing how this Update to the collection was defined, it appears PowerApps compensates for those fields to appear in the birth of the collection.
RusselThomas 4,014 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @JeffTex ,
Indeed - I've used this same behaviour to pre-create empty constructs - for example;
If(1=2, ClearCollect(colEmptyTable, Table({col1: "string", col2: 1, col3: true}))
Because 1 will never = 2, this will never process - but it will still create the contstruct and type the columns without populating it with anything.
And if you add anything anywhere to that collection using similar logic that will never 'run';
If(1=2, Collect(colEmptyTable, {col4: "something", col5: Today()}))
this will still create the columns in the collection, even though it doesn't execute the function and actually create the data...
Kind regards,
RT
JeffTex 57 on at

Like
a
(1)

Report
Copy link

Link copied!
I have learned a few lessons here:
The search feature is a life saver
There is always a rogue collection somewhere 🤣
The SharePoint ID column is an enemy (just make your own numeric PK and write in your patches the next incremental value with Error check for conflict / collision etc.)
I will never know if my hypothesis was correct because I changed two things at once
found the deeply buried rogue collection left from testing (before I built these procedures into central function calls)
reworked the UpdateIf() to reference only the target columns of the collection
My gut feeling is this:
My hypothesis was completely wrong and it was as simple as the rogue collection. Actually yes. I just tested by rewiring the UpdateIf() back to simply 'Change' and it wont even work that way anymore.... Why you ask.... because. Those extra columns no longer exist (as it should be).