cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
VivekBhatt
Advocate I
Advocate I

Fetch Column Names

Hi All,

 

I want to create a collection in which i can fetch Only column names from a SQL Table 

 

Column 1
Column 2

Column 3

5 REPLIES 5
FlowMohit
Helper I
Helper I

hi @VivekBhatt 

 

As per my knowledge using powerapps alone, it is not possible. You need to create a flow with Execute a SQL Query(V2) action by passing this query in query option-

Flow should look like this - 

 

Capture.PNG

 

It will give you the information about table schema. Later you can transform it and pass it back to you powerapps.

 
timl
Super User
Super User

Hi @VivekBhatt 

Unfortunately, there's no way in PowerApps to retrieve column names.

There's a post in the ideas forum that you can vote for here.

https://powerusers.microsoft.com/t5/Power-Apps-Ideas/Column-Metadata/idi-p/37480 

To work around this, I would create a view that queries the system tables to retrieve the column names.

CREATE VIEW vwTableColumns
AS
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'YourTableName'


You can then add this view as a data source to your app, and use that to create your collection.

ClearCollect(yourCollection, vwTableColumns)

You can also check out @ThatAPIGuys video here.

https://www.youtube.com/watch?v=Qda7XjyfBfM&feature=youtu.be&t=780 

Here, Vivek shows how to use Flow to retrieve the first record, and to call Substitute/Replace to retrieve the column names.

DragonLord
Helper III
Helper III

 have this done please, i need it

mhayes2
Frequent Visitor

I managed to do this using the code from this site :

https://www.matthewdevaney.com/powerapps-collections-cookbook/get-collection-column-names/

 

//Create a collection

ClearCollect(myTrucks5, {Year: 2020, Make: "Ford", Model: "F150", Mileage: 12343}, {Year: 2018, Make: "Chevy", Model: "Silverado", Mileage: 170567}, {Year: 2020, Make: "Ford", Model: "F150", Mileage: 98765}, {Year: 2017, Make: "Nissan", Model: "Frontier", Mileage: 123975}, {Year: 2016, Make: "GM", Model: "Yukon", Mileage: 98753} );

// Get column names

Set( myJSON, Distinct( Ungroup( MatchAll( JSON( myTrucks2, JSONFormat.IgnoreBinaryData ), "([^""]+?)""\s*:" ).SubMatches, "SubMatches" ), Value ) )

 

 

Ondrix
Frequent Visitor

Awesome, thanks. Works flawless!👍

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

Power Platform release plan for the 2022 release wave 2 describes all new features releasing from October 2022 through March 2023.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Top Solution Authors
Users online (3,088)