Unanswered

ShowColumns for column type DropDown in a SharePoint list

(0) Share

Report

Posted on by NewBee117

247

How do I pull only the selected column from a SharePoint list into a collection? ShowColumns can't see the dropdown column type. It said, "the specified column does not exist".

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (1)

Pstork1 68,697 Most Valuable Professional on at

Like (0)

Report

SharePoint doesn't have a column type of dropdown. There is are several column types that show up as a dropdown in Power Apps. For example, Choice columns are displayed as dropdowns. But those are available using ShowColumns(). Are you sure you have the right column name? If you go to list settings and edit the column you will see the internal column name at the end of the URL. This is sometimes different than the column display name. The column name may also be surrounded by quotes.

Was this reply helpful? Yes No
NewBee117 247 on at

Like (0)

Report

Here is an example, let me know what is the correct column name to use:

1. SharePoint list Dropdown control:

2. In the gallery, I am able to pull Yes or No value by using ThisItem.OfficeOpen.Value.

3. ShowColumns gives an error: The specified column "OfficeOpen' does not exist.

Thanks!

Was this reply helpful? Yes No
Verified answer

Drrickryp Super User 2024 Season 1 on at

Like (0)

Report
@NewBee117

Sharepoint columns have a display name and an internal name. These can be confused in PowerApps as ShowColumns() looks for the internal name. To find the internal name for the column

You can quickly determine the internal name of a field using the UI: Open the List Settings page Under the Columns section, select a column to view the Edit Column page The URL of this page includes the internal name in the query string. For example, the URL for the Created By field includes the following query string List=%7BF641CEF1%2DCDE2%2D49E1%2D9800%2D861A408EF890%7D&Field=Author. The value for the Field parameter, Author, is the internal name

Sometimes the display name is the same as the internal name and sometimes it isn't. Even weirder, the display name works fine in filters but errors out in ShowColumns(). When you get the error that the field doesn't exist, go back to SharePoint and find the internal name and try that.
In the screenshot below, when I first created my list, SharePoint automatically renamed the Title column to a display name of first_name, as that was the first column in the data I imported. For a table shaping function like ShowColumns(), I had to go back and find out the internal name to use in the formula. For more tips on using SharePoint and PowerApps see my post https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/The-Ten-Commandments-of-SharePoint-in-PowerApps/ba-p/848842 . A more comprehesive explanation of SharePoint and PowerApps is @WarrenBelz 's blog here https://www.practicalpowerapps.com/ . IMHO, it's a must-read for everyone using the SharePoint connector in PowerApps.

Was this reply helpful? Yes No
NewBee117 247 on at

Like (0)

Report

@Drrickryp Amazing, the internal name was the fix! Thank you so much. 🙂
My next issue, not sure I should start a new thread or not, JSON() returns data from the ShowColumns collection is not usable, how do I get rid of all the "Value" text?

Here is the ClearCollect, is it possible to pick only the value of each column?

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

@NewBee117

Lets ask @Pstork1 , he's much better at that than me.

Was this reply helpful? Yes No
Pstork1 68,697 Most Valuable Professional on at

Like (0)

Report

For complex columns like Choice or Yes/No columns (dropdown columns) there is no way to get rid of that value:. The column stores an object. But the object in the column is still usable. Just like you use ThisItem.OfficeOpen.Value the same syntax will work when accessing a column in the collection.

We could provide a better answer if you would explain why you want it in a collection. If you are creating a collection to generate JSON and send it to Power Automate then it will work fine with the {value: "item"} syntax in place. If you are trying to do something else let us know and we'll try to help.

Was this reply helpful? Yes No
NewBee117 247 on at

Like (0)

Report

@Pstork1 My goal is to export selected columns of the SharePoint list to Excel/csv file for download, within PowerApps. Users do not have the link to the SharePoint site. PowerApps display the data just fine, wish I could export what is showing in the gallery to Excel. I am open to any solution to make this work.

Was this reply helpful? Yes No
Pstork1 68,697 Most Valuable Professional on at

Like (0)

Report

Use JSON() to send the collection to a Flow. The flow will have a much easier time converting the JSON array into a CSV file anyway. You can then send the link or the CSV contents back to Power Apps for download.

Was this reply helpful? Yes No
NewBee117 247 on at

Like (0)

Report

That is what I did, scroll up to see the table created by Flow. Can Flow remove everything other than the actual value of the field? also, need to rename the column header as well.

Was this reply helpful? Yes No
Pstork1 68,697 Most Valuable Professional on at

Like (0)

Report

Yes, the Data Select action in flow can easily handle both those issues. There is also a Data operation that will automatically convert the array to a CSV

Was this reply helpful? Yes No