Answered

Allow Users to Choose Columns To Add to a Collection

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hello!

I am trying to create collection where a user can select which columns to include or exclude in the collection from a Gallery.

Currently I allow a user to filter a gallery and based on the filters selected I allow the user to export the data to an excel spreadsheet. The spreadsheet exports with the same default columns every time time (I have chosen to show these columns as default). This is working like a charm.

I would like to add additional functionality that would allow a user to choose the columns they would like to include or exclude in the export.

I am hoping to achieve this by allowing the user to select a column to add to the collection for export by selecting a checkbox. If true include that column in the collection, if false do not include it.

Below is the code I am currently using and that is working. Hoping to add functionality to allow a user to choose the columns they want to add to the collection instead of the default in the code below.

ClearCollect(_ExportCollection2,ShowColumns(GalleryA.AllItems,"{Name}", "Launch_x0020_Date","Prime"));

ClearCollect(_ExportCollection,RenameColumns(_ExportCollection2,"{Name}","Initiative Name", "Launch_x0020_Date","Launch Date", "Prime", "Prime"));

Set(varFormattedJSON, JSON(_ExportCollection, JSONFormat.IndentFour));

TrackerExporttoCSV.Run(varFormattedJSON)

;Refresh('Out Puts')

; UpdateContext({_Export: "true"})

; Select(ButtonMain)

I hope this makes sense!

Thank you!!!

Categories:

Building Power Apps

All responses (4)

Answers (1)

Sort by

v-siky-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Anonymous ,

Since ShowColumns() function only support literal Text in Column Parameter, hence we cannot apply logic into the ShowColumns().

We have to use If() logic to list Five possible cases(3 columns) as below, although it may looks not clear.

If( Checkbox1.Value=true&&Checkbox2.Value=false&&Checkbox3.Value=false, ClearCollect(_ExportCollection2,ShowColumns(GalleryA.AllItems,"{Name}")), Checkbox1.Value=true&&Checkbox2.Value=true&&Checkbox3.Value=false, ClearCollect(_ExportCollection2,ShowColumns(GalleryA.AllItems,"{Name}", "Launch_x0020_Date")), Checkbox1.Value=true&&Checkbox2.Value=true&&Checkbox3.Value=true, ClearCollect(_ExportCollection2,ShowColumns(GalleryA.AllItems,"{Name}", "Launch_x0020_Date","Prime")), Checkbox1.Value=false&&Checkbox2.Value=true&&Checkbox3.Value=true, ClearCollect(_ExportCollection2,ShowColumns(GalleryA.AllItems,"Launch_x0020_Date","Prime")), Checkbox1.Value=false&&Checkbox2.Value=false&&Checkbox3.Value=true, ClearCollect(_ExportCollection2,ShowColumns(GalleryA.AllItems,"Prime")) )

Hope this helps

Sik
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

I thought about this approach as well. The problem is there are another 20 columns that I want to include and the If statement would need to be written multiple times to cover the dozens of possible combinations.

I was hoping to create a collection based on which checkbox is chosen, essentially move columns in an out of a collection based on whether a checkbox is selected.

I thought this might be achieved by creating a collection specifically for this task as a first step.

Thank you!
Verified answer

v-siky-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Anonymous ,

Your words inspired me, please try this code, recreate a new collection based on gallery items.

Clear(colName); ForAll( GalleryName.AllItems, Collect( colName, { 'Initiative Name': If( Checkbox1.Value, '{Name}' ), 'Launch Date': If( Checkbox2.Value, 'Launch_x0020_Date"' ), Prime: If( Checkbox3.Value, Prime ), ... // add more columns here } ) )

Hope this helps.

Sik
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

In case this helps someone, I had a similar issue. I wanted to allow users to select the columns they wanted, in the order they wanted, and export the data they just filtered to CSV from the Power App.

If you know in advance what columns and the order they'll be in all you need to do is create a JSON string from the collection like this:

Set(jsonString, JSON(YourCollection, JSONFormat.IndentFour));

Then call a Flow and pass the jsonString and carry out the logic there in whatever way you want.

But if you want to allow users to choose the columns they want and set the order, it's just a bit trickier. You need a separate collection of column data, such as column name, and order number, etc, which you can present to users with a gallery control. Then, just add another jsonString for the column data, and pass both the export data and column data as 2 separate JSON strings to your flow, where you can combine the two sets into a single CSV file that only includes the columns the users wanted and in the order they set.

Good luck!