Setting ComboBox DefaultSelectedItem from ExCel Table

(0) Share

Report

Posted on by CoolingPowerApp

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 -

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!

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

JRaasumaa 1,325 on at

Like (0)

Report

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

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-

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?

Was this reply helpful? Yes No
CoolingPowerApp 31 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

CoolingPowerApp 31 on at

Like (0)

Report

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

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

Was this reply helpful? Yes No