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

Dropdown text to SQL

Hello,

 

I have SQL table with couple fields, they are char(10) (I tried different data type as well: ntext,nvarchar(50))

 

In my app, I have a form that write/update the record in SQL.

 

Items for each dropdown within the form :

["N/A","1","2","3"]

The fields are used for scoring N/A used if not scored at this time, and of course 1,2,3 when there is score to be applied.

 

Here is my issue. When entering new record to SQL it adds N/A as the entry if that's what's chosen (also a default option). Once the field gets updated by the form to one of the numbers 1,2 or 3 it also changes the field in SQL. But when I want to change back to N/A, it does not, it just goes back to previous value.

 

I tried changing the "Update" value on the form item to:

Text(DataCardValue4.Selected.Value,"[$-en-US]")

I tried changing different SQL data type.

Capture.JPG

Thank you.

 

8 REPLIES 8
Community Support Team
Community Support Team

Re: Dropdown text to SQL

Hi @cymek11 ,

 

Do you want to write dropdown’s text to your sql?

Do you set one Primary key in your table? I think if you use sql table Primary key is needed,please check whether there is one in your sql table if not please add one and then test it.

I test on my side as next, and the test is ok, not reproduce your issue, please try the following workaround to test.

DataSource:

20190222writetosql1.png

App Test:

20190222writetosql2.jpg

 

Result test:

20190222writetosqltresulttgif.gif

20190222writetosql3.png

 

 

Hope this can be helpful.

Best Regards.

Yumia

cymek11
Level: Powered On

Re: Dropdown text to SQL

I have primary ID in my SQL table. It's set with automatic increment each time a record is added.

 

I can see you can write 'N/A" in the field. I get the same results when writing the value to SQL for the first time. The issue exist when you pull-up an existing record, and you try to change the value from 1,2 or 3 back to 'N/A', and you submit the Form to update the record. No problem when changing from 'N/A' to a number. It only doesn't work when switching back to N/A.

 

In your example are you using a Form to update the record in SQL?

 

I hope that clarifies.

 

Thanks.

emh
Level 8

Re: Dropdown text to SQL

Hello,

 

If I understand correctly, the UPDATE property of the datacard should reference the dropdown. It looks like your UPDATE property is referencing the datacardvalue.

emh
Level 8

Re: Dropdown text to SQL


@cymek11 wrote:

I have primary ID in my SQL table. It's set with automatic increment each time a record is added.

 

I can see you can write 'N/A" in the field. I get the same results when writing the value to SQL for the first time. The issue exist when you pull-up an existing record, and you try to change the value from 1,2 or 3 back to 'N/A', and you submit the Form to update the record. No problem when changing from 'N/A' to a number. It only doesn't work when switching back to N/A.

 

In your example are you using a Form to update the record in SQL?

 

I hope that clarifies.

 

Thanks.




cymek11
Level: Powered On

Re: Dropdown text to SQL


@emh wrote:

Hello,

 

If I understand correctly, the UPDATE property of the datacard should reference the dropdown. It looks like your UPDATE property is referencing the datacardvalue.


The DataCard UPDATE is set to: DataCardValue3 (DataCardValue3.Selected.Value),  which is the dropdown within the DataCard with ["N/A","1","2","3"] as items.

 

Thanks

Community Support Team
Community Support Team

Re: Dropdown text to SQL

Hi @cymek11 ,

 

Yes,you are right. I used the Eidt Form to test.

 

Best Regards.

Yumia

Highlighted
cymek11
Level: Powered On

Re: Dropdown text to SQL


@v-yuxima-msft wrote:

Hi @cymek11 ,

 

Yes,you are right. I used the Eidt Form to test.

 

Best Regards.

Yumia


Ok, great.

So now on your form pull-up the record ID=2 and set the Test1 to N/A and update the record with SubmitForm(Form1).

Did SQL side changed to N/A?

 

Thanks

Community Support Team
Community Support Team

Re: Dropdown text to SQL

Hi @cymek11 ,

 

Yes, I use Edit form. And it works ok.

Could you please try to use one edit form and then change the control type of the  (of course you need to unloack the datacard firstly) to choose allowed values as my screeshots shows  next and then change its items to what you want to write to your sql?

20190222writetosql2.jpg

20190227writetosql.PNG

 

Best Regards.

Yumia

Helpful resources

Announcements
thirdimage

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

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (3,690)