Get and save data from multipleselection combobox

[espenjor]

How can save all the selected data from a combobox with multiple values to a table (in my case SQL table) and  the retrieve it later (from a table)?

BTW: Are there any rules for what kind if fields that need to be used to store such data in the ?

[v-xida-msft · Accepted solution]

Hi @espenjor ,

Could you please share more details about the formula you typed within the Items property of the ComboBox?

Do you want to save the selected options within the ComboBox back to a field of single one record in your SQL Table?

Firstly, the selected options within the ComboBox returns a Table value, currently, there is no data type supported within SQL Table to store a table value directly.

As an alternative solution, you could consider concatenate these selected options within the ComboBox into a single one string with a specific separator (e.g. &), then save the single one string back to your SQL Table. When you want to retrieve the saved value from your SQL Table, and display it within the ComboBox, you could consider split the saved string value into a table value based on the specific separator (e.g. &).

I have made a test on my side, please consider take a try with the following workaround:

I assume that you use Patch function to patch data back to your SQL Table, please set the OnSelect property of the "Submit" button to following:

Patch(
      '[dbo].[SQLTable]',
      Defaults('[dbo].[SQLTable]'),
      {
        ...,
        TextTypeColumn: Concat(ComboBox1.Selecteditems, Value & "&"),
        ...
      }
)

If you want to save each selected option from your ComboBox as a separated row in your SQL Table, please take a try with the following formula:

ForAll(
       ComboBox1.SelectedItems,
       Patch(
             '[dbo].[SQLTable]',
              Defaults('[dbo].[SQLTable]'),
              {
                ...,
                TextTypeColumn: Value,
                ...
              }
       )
)

Note: Please add a Text type column (varchar, nvarchar) in your SQL Table to store the concatenated selected options from your ComboBox.

If you want to display the saved options within the ComboBox when you edit a specific record, please consider set the DefaultSelectedItems property of the ComboBox to following:

ForAll(
        Filter(Split(LookUp('[dbo].[SQLTable]', PrimaryKeyColumn = "Specific Value", TextTypeColumn), "&"), !IsBlank(Result)),
        {
           Value: Result
        }
)

In addition, if you use Edit form to collect your entry data, and submit the form data using SubmitForm function, please consider unlock the Text Type field data card which contains the ComboBox control in your Edit form, then set the Update property of the Data card to following:

Concat(ComboBox1.SelectedItems, Value & "&")

Set the DefaultSelectedItems property of the ComboBox to following:

If(                                      /* <-- When you edit a specific record, display the saved options within the ComboBox */
   EditForm1.Mode = FormMode.Edit,
   ForAll(
           Filter(Split(ThisItem.TextTypeColumn, "&"), !IsBlank(Result)),
           {
              Value: Result
           }
   )
)

Please consider take a try with above solution, then check if the issue is solved.

Best regards,

[TWolsten]

Hi Kris,

I have tried your solution and it works when viewing the data but if I edit the item without changing the ComboBox then when I save the item the ComboBox values are blanket and removes the previously selected values?

Any ideas please?