cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chandu
Helper III
Helper III

How to save one datacardvalue into multiple rows in SQL Server

Based on the Combobox Selection in the below image, the TASK_NUM Datacardvalue is getting value. Now, I am having two Issues. They are:

1. In Task_ID Datacardvalue, based on Combobox selection the ID should display like 4, 5. But as this field datatype is INT in SQL, I am not able to save all the ID's of Combobox selection.

2. When I click Submit button the data is saving in a single row in SQL. But, TASK_NUM having two values and I want to save that as two different rows in SQL Server respective to its PROJECT_ID.

PTIPDI.PNG

Can anyone tell me how to solve these Issues.

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

Hi @Chandu ,

Do you want to create individual record in your SQL Table based on the selected options in your CombBox?

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

 

Regarding the needs that you mentioned, I think the SubmitForm function could not achieve your needs, instead, you should use Patch function to submit your form data back to your SQL Table.

 

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

1. Remove the TASK_ID and TASK_NUM field data card from your Edit form. Just remain the common field data cards in the Edit form for multiple Task Nums.

2. Set the OnSelect property of the "Submit" button to following:

ForAll(
       ComboBox1.SelectedItems As LoopRecord,
       Patch(
             '[dbo].[SQLTable]',
             Defaults('[dbo].[SQLTable]'),
             EditForm1.Updates,
             {
                TASK_NUM: LoopRecord.TaskNumColumn,
                TASK_ID: LoopRecord.TaskIDColumn
             }
       )
)

Note: The TaskNumColumn and the TaskIDColumn represents the columns from the records selected inside your ComboBox. Please replace it with actual column name from your side.

 

Please try above solution, then check if the issue is solved.

 

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

3 REPLIES 3
v-xida-msft
Community Support
Community Support

Hi @Chandu ,

Do you want to create individual record in your SQL Table based on the selected options in your CombBox?

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

 

Regarding the needs that you mentioned, I think the SubmitForm function could not achieve your needs, instead, you should use Patch function to submit your form data back to your SQL Table.

 

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

1. Remove the TASK_ID and TASK_NUM field data card from your Edit form. Just remain the common field data cards in the Edit form for multiple Task Nums.

2. Set the OnSelect property of the "Submit" button to following:

ForAll(
       ComboBox1.SelectedItems As LoopRecord,
       Patch(
             '[dbo].[SQLTable]',
             Defaults('[dbo].[SQLTable]'),
             EditForm1.Updates,
             {
                TASK_NUM: LoopRecord.TaskNumColumn,
                TASK_ID: LoopRecord.TaskIDColumn
             }
       )
)

Note: The TaskNumColumn and the TaskIDColumn represents the columns from the records selected inside your ComboBox. Please replace it with actual column name from your side.

 

Please try above solution, then check if the issue is solved.

 

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

In ListBox I am having the formula like:

Listbox.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

In an EditForm I am having (Project_ID, Task_ID, TEST_ID). For Project_ID, I have written the formula as shown in Image and it is getting the Project_ID

Project_ID.PNG

For Task_ID, the below formula is not working. Based on the Listbox (Multiple Selection) it should display as (1,2) and same with TEST_ID.

Task_ID.PNG

Task_ID and TEST_ID datatype is INT in SQL. So, how should I save the ID as (1,2,3) based on Listbox slection.

Hi @Chandu ,

Have you tried the solution I provided above?

 

Due to the TASK_ID and TEST_ID field are both Int type column in your SQL Table, so you could not display multiple TASK_ID values within the TASK_ID data card, and save it back to your SQL Table.

 

As an possible solution, I think the Patch function could achieve your needs. Have you tried the solution I provided above?

1. Remove the TASK_ID and TASK_NUM field data card from your Edit form. Just remain the common field data cards in the Edit form for multiple Task Nums.

2. Set the OnSelect property of the "Submit" button to following:

ForAll(
       ComboBox1.SelectedItems As LoopRecord,
       Patch(
             '[dbo].[SQLTable]',
             Defaults('[dbo].[SQLTable]'),
             EditForm1.Updates,
             {
                TASK_NUM: LoopRecord.TaskNumColumn,
                TASK_ID: LoopRecord.TaskIDColumn
             }
       )
)
Note: The TaskNumColumn and the TaskIDColumn represents the columns from the records selected inside your ComboBox. Please replace it with actual column name from your side.

 

Due to the TASK_ID and TEST_ID field are both Int type column in your SQL Table, so the TASK_ID and TEST_ID field data card in your Edit form could only accept integer value, if you want to display the TASK_ID values within the  TASK_ID field data card, please unlock the TASK_ID data card, then set the Format property of the TASK_ID Text box to following:

TextFormat.Text

then set the Default property of the TASK_ID Text Box to following:

Concat(ListBox1.SelectedItems, TASK_ID & ", ")

then these TASK_ID values would be displayed within the TASK_ID Text box, but you could not save the form data back to your SQL Table (data type is not compatible).

 

Please try the solution I provided above to save your form data back to SQL Table using Patch function:

ForAll(
       ListBox1.SelectedItems As LoopRecord,
       Patch(
             '[dbo].[SQLTable]',
             Defaults('[dbo].[SQLTable]'),
             EditForm1.Updates,
             {
                TASK_NUM: LoopRecord.TASK_NUM,
                TASK_ID: LoopRecord.TASK_ID
             }
       )
)

 

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.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (31,529)