cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
espenjor
Helper II
Helper II

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
v-xida-msft
Community Support
Community Support

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

2 REPLIES 2
v-xida-msft
Community Support
Community Support

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.

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?

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,534)