cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CoolingPowerApp
Helper I
Helper I

Setting ComboBox DefaultSelectedItem from ExCel Table

Hey there,

 

I have a report writing app I am working on.

 

My main goal is to have a field that the user can select multiple values from, be able to submit that report, store the data in an ExCel table, later select that stored report from the gallery, and show their old selections in the combobox/listbox/whatever. 

 

My app doesn't use EditForms and I am having a little trouble getting my ComboBoxes to load stored data.

 

I have a ComboBox like this -

ComboBox.jpg

 

I collect these selections to an ExCel table in OneDrive with a formula like

 

Collect(Table1,{Selections:Concat(ComboBox1.SelectedItems,Concatenate(Text(Value),","))})

 

This code gives me these results-

ExCel Save.jpg

 

I try to take this data back in to the ComboBox's DefaultSelectedItem with a formula like

 

LookUp(Table1,ReportID=DataCardValue1.Text,Selections)

 

Taking the text value right back in to DefaultSelectedItem does not work. DefaultSelectedItem requires Table data type and I believe all of the columns in ExCel tables have to be Text data type. 

 

I am looking for a way to turn that Text data back in to Table data, a different way to store the data so it stays as Table data, or an all together different solution. I am open to all ideas and really appreciate the help.

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions

Sorry for being helpless in my other post. I sat back and thought about what you said and was able to figure out how to collect from an Excel table. This was able to work somewhat, and will get me where I need to go for the most part.

 

What I found was that by collecting that cell and using it as the DefaultItemProperties it would return something like this

New ComboBox.jpg

So it puts the selected values as a single string in the box. It would be nice to have them broken up but it isn't super neccesary.

I used this code to collect from the Excel Table-

 

ClearCollect(trust,LookUp(Table1,Title=DataCardValue1.Text,Selections))

 

And my code for DefaultSelectedItem is-

trust.Value

 

The table looks like this

Trust.jpg

 

There may be a way to break up the string or something based on the commas. I will look into it.

View solution in original post

3 REPLIES 3
JRaasumaa
Memorable Member
Memorable Member


@CoolingPowerApp wrote:

Hey there,

 

I have a report writing app I am working on.

 

My main goal is to have a field that the user can select multiple values from, be able to submit that report, store the data in an ExCel table, later select that stored report from the gallery, and show their old selections in the combobox/listbox/whatever. 

 

My app doesn't use EditForms and I am having a little trouble getting my ComboBoxes to load stored data.

 

I have a ComboBox like this -

ComboBox.jpg

 

I collect these selections to an ExCel table in OneDrive with a formula like

 

Collect(Table1,{Selections:Concat(ComboBox1.SelectedItems,Concatenate(Text(Value),","))})

 

This code gives me these results-

ExCel Save.jpg

 

I try to take this data back in to the ComboBox's DefaultSelectedItem with a formula like

 

LookUp(Table1,ReportID=DataCardValue1.Text,Selections)

 

Taking the text value right back in to DefaultSelectedItem does not work. DefaultSelectedItem requires Table data type and I believe all of the columns in ExCel tables have to be Text data type. 

 

I am looking for a way to turn that Text data back in to Table data, a different way to store the data so it stays as Table data, or an all together different solution. I am open to all ideas and really appreciate the help.

 

Thanks!


 

What happens if you try to load the selected items into a collection value and then use that collection as the DefaultSelectedItem?

 

 

First, I appreciate your response. Second, I actually don't know how to load data from an ExCel table as collection values. I think that may do the trick though.

Sorry for being helpless in my other post. I sat back and thought about what you said and was able to figure out how to collect from an Excel table. This was able to work somewhat, and will get me where I need to go for the most part.

 

What I found was that by collecting that cell and using it as the DefaultItemProperties it would return something like this

New ComboBox.jpg

So it puts the selected values as a single string in the box. It would be nice to have them broken up but it isn't super neccesary.

I used this code to collect from the Excel Table-

 

ClearCollect(trust,LookUp(Table1,Title=DataCardValue1.Text,Selections))

 

And my code for DefaultSelectedItem is-

trust.Value

 

The table looks like this

Trust.jpg

 

There may be a way to break up the string or something based on the commas. I will look into it.

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

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.

Users online (1,830)