cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
krickard
Helper III
Helper III

Collecting only certain columns with ShowColumns and a filter

I have a few statements that I'm attempting to merge together that aren't passing syntax validation. I'm unsure how to best nest these statements. 

 

First off I have a SP list of data that I want to copy to a collection. This works by itself except that it pulls all kinds of strange columns names like "OData_..." and even changes exiting SP column names to cryptic ones such as "ooyw". 

 

ClearCollect(colsavedsurvey,Filter(savedsurvey,saved_surveyname = ThisItem.Result));

 

I came across a post that talked about using ShowColumns to collect just the columns I want from the SP list. Something like this...

ClearCollect(stage,ShowColumns(colsavedsurvey, "saved_surveyname", "saved_category", "saved_question", "saved_qid", "saved_answer1", "saved_answer1points", "saved_answer2", "saved_answer2points", "saved_answer3", "saved_answer3points", "saved_answer4", "saved_answer4points", "saved_answer5", "saved_answer5points"));

BTW... secondary problem, it does not like this ShowColumns code, says the columns don't exist.  

 

So the ask is... How do I use a collect with a filter to only select certain columns from a SP list into a collection?  I've tried this as well, with no good results...

ClearCollect(colsavedsurvey,Filter(savedsurvey,saved_surveyname = ThisItem.Result), ShowColumns(colsavedsurvey, "saved_surveyname", "saved_category", "saved_question", "saved_qid", "saved_answer1", "saved_answer1points", "saved_answer2", "saved_answer2points", "saved_answer3", "saved_answer3points", "saved_answer4", "saved_answer4points", "saved_answer5", "saved_answer5points"));

 

Thanks in advance for your thought cycles. Let me know if there is additional information that would help. 

3 ACCEPTED SOLUTIONS

Accepted Solutions
Alex_10
Super User
Super User

What if you try:

ClearCollect(colsavedsurvey, ShowColumns(Filter(savedsurvey,saved_surveyname = ThisItem.Result), 'saved_surveyname', 'saved_category', 'saved_question', 'saved_qid', 'saved_answer1', 'saved_answer1points', 'saved_answer2', 'saved_answer2points', 'saved_answer3', 'saved_answer3points', 'saved_answer4', 'saved_answer4points', 'saved_answer5', 'saved_answer5points'));

View solution in original post

edited

 

try to replace single quotes with double quotes for column names

 

View solution in original post

just add "OData_" to the columns that start with underscore. 

Example: "_x0074_g36" becomes "OData__x0074_g36"

 

PS. I am just curious. Where did you get that list?

View solution in original post

10 REPLIES 10
Alex_10
Super User
Super User

What if you try:

ClearCollect(colsavedsurvey, ShowColumns(Filter(savedsurvey,saved_surveyname = ThisItem.Result), 'saved_surveyname', 'saved_category', 'saved_question', 'saved_qid', 'saved_answer1', 'saved_answer1points', 'saved_answer2', 'saved_answer2points', 'saved_answer3', 'saved_answer3points', 'saved_answer4', 'saved_answer4points', 'saved_answer5', 'saved_answer5points'));

Thanks @Alex_10... it seems to have gotten rid of the crazy column names in the collection. However, I'm still seeing the "Name isn't valid. The identifier isn't recognized" on the ShowColumn section of the code. Any thoughts on why this might be? 

 

krickard_0-1598038656371.png

 

edited

 

try to replace single quotes with double quotes for column names

 

I had thought of that a little bit ago and it does change the error to now saying the specific column name doesn't exist. Such as "The specified column 'saved_qid' doesn't exist" for instance.  

you need to specify columns' internal names (not display names). Internal name is in black (Display name is in red)internal names.png

Hi @krickard ,

Regarding the screenshot that you mentioned, I think there is something wrong with the column names ("saved_surveyname", "saved_qid", "saved_answer1points", "saved_answer2points", "saved_answer3points", "saved_answer4points", "saved_answer5points") specified within your ShowColumns function.

 

Please make sure these specified column names are existed in your SP List, and their internal name are identical to these displayed column names.

5.JPG

Actually, within canvas app, it would reference the Internal Column Name of columns in a SP List, so please use the internal column name of columns in your ShowColumns function.

 

Actually, when you use ShowColumns function in a canvas app, after you specify the data source, these available column names would be listed as below:

6.JPG

In addition, you should use double quotes ("") to wrap these columns in your ShowColumns function instead of single quote (''). Please modify your formula as below:

ClearCollect(
             colsavedsurvey,
             ShowColumns(
                         Filter(savedsurvey, saved_surveyname = ThisItem.Result),
                         "saved_surveyname",  // please wrap these column names using double quotes ("") rather than single quote
                         "saved_category",
                         "saved_question",
                         "saved_qid",
                         "saved_answer1",
                         ...
                         ...
             )
)

 

Please try above solution, then check if the issue is solved.

 

Regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

If I understand SharePoint correctly, you can't change internal column names, only the display name. Changing the display name to match the cryptic internal name doesn't make any sense. 

 

In the ShowColumns function I've gone through and used the internal names as shown below. However, the ones that start with "_x" still say they don't exist, while the other internal names are working now. 

 

Capture.PNG

Capture1.PNG

just add "OData_" to the columns that start with underscore. 

Example: "_x0074_g36" becomes "OData__x0074_g36"

 

PS. I am just curious. Where did you get that list?

Thanks @Alex_10 and @v-xida-msft your collection of replies helped me get it to work. Have I mentioned how much I dislike SP? Unfortunate that they made Azure SQL premium. 😞

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 (2,744)