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

Get and save data from multipleselection combobox

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 ?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Get and save data from multipleselection combobox

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Community Support Team
Community Support Team

Re: Get and save data from multipleselection combobox

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
thirdimage

Power Platform 24

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

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