Collecting only certain columns with ShowColumns and a filter

[krickard]

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. 

[Alex_10 · Accepted solution]

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

[krickard]

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? 

[Alex_10 · Accepted solution]

edited

try to replace single quotes with double quotes for column names

[krickard]

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.  

[Alex_10]

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

[v-xida-msft]

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.

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:

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,

[krickard]

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. 

 

 

[Alex_10 · Accepted solution]

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?

[krickard]

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. 😞