cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JeffTex
Helper I
Helper I

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

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
    )
)

JeffTex_0-1658244478080.png

 

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

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

JeffTex_1-1658244719246.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RusselThomas
Microsoft
Microsoft

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 

View solution in original post

4 REPLIES 4
RusselThomas
Microsoft
Microsoft

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 

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
Microsoft
Microsoft

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
Helper I
Helper I

I have learned a few lessons here:

  1. The search feature is a life saver
  2. There is always a rogue collection somewhere 🤣
  3. 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.)
  4. I will never know if my hypothesis was correct because I changed two things at once
    1. found the deeply buried rogue collection left from testing (before I built these procedures into central function calls)
    2. 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).

Helpful resources

Announcements
Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Users online (5,178)