cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CoolingPowerApp
Level: Powered On

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
CoolingPowerApp
Level: Powered On

Re: Setting ComboBox DefaultSelectedItem from ExCel Table

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
Level 10

Re: Setting ComboBox DefaultSelectedItem from ExCel Table


@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?

 

 

Highlighted
CoolingPowerApp
Level: Powered On

Re: Setting ComboBox DefaultSelectedItem from ExCel Table

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.

CoolingPowerApp
Level: Powered On

Re: Setting ComboBox DefaultSelectedItem from ExCel Table

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

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (4,966)