cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Format decimal and float numbers

Dear reader,

 

I have a question regarding creating records into a SQL server 2016 on premise database.

 

I have a column with type: float, Now I have created a form which inserts records into the database. The format for the column is now TextFormat.Number. How can I modify this format in a way that it can handle floats with decimal ','.

 

Now it just gives an syntax error on submitting the record into the database.

 

Alternative, I saw that the budget tracker has something like a solution to it since, I can only type in numbers. The format looks like text but when I enter letters the trackers just gives an error.

 

 

How can I solve this, without changing the database type to nvarchar.

 

Best regards.

14 REPLIES 14
Community Support
Community Support

Hi Yme,

 

Please check the document regarding text function in PowerApp:

Text function in PowerApps

Refer to the Examples (Number ) part. For decimal, it should be OK to use the Text function below:

Text( Number, "####.#" )

Latter is the number format.

Regarding Float format,

"Conversion of float values that use scientific notation to decimal or numeric is restricted to values of precision 17 digits only. Any value with precision higher than 17 rounds to zero."

I think if we just define the number to show with Number placeholders within 17 digits, it should work with the float format.

Please take a try with the formula above.

 

In addition, could you please explain a bit regarding TextFormat.Number here?

 

Currently it is not available for PowerApp to connect to SQL server database with a table that has no text column defined.

If you need any further assistance on this, then please post back.

Regards

 

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

Hi @Yme

I am in Europe and have the same issue with decimal ',' and not '.'

One issue is that you should submit a number value to your database and not text, and this can be done with the Value function.

Details are here: https://powerapps.microsoft.com/en-us/tutorials/function-value/

 

In your case assuming you are using a text input for collecting user input and you are using a patch to your database, then this would be the syntax for the SaveButton:

 

SaveButton.OnSelect = Patch(MyDataSource, {ID: MyRecordID}, {MyFloatingColumn: Value(MyTextInput.Text, "it-IT")})

SaveButton.Disabled = !IsNumeric(MyTextInput.Text)

The second piece will disable the save button if the text input contains non-numeric characters, however it will not check to see if the input is a proper number.  All should be clear in the image below, but please let me know if you need more help.

 

CaptureValue.JPG

PS I have looked at budget tracker and it seems quite unsophisticated, as it does not reformat your number input when you are done (in other words if you input 213345566,999323 then it just stays that way).

What I am experimenting with now is to use a context variable to store the number and use it in the Default property of the text input to show a formatted number once the user has finished inputting it, by using the OnChange property of the text input.

Here is the code and the screenshot below.  If you are editing an existing record, then you need to modify the first line to set another context variable (NumberIsChanged) to true and the second line to get your source number if NumberIsChanged is false.  Happy to provide further details.

 

TextInput3.OnChange = UpdateContext({MyNumber: Value(TextInput3.Text, "it-IT")})

TextInput3.Default = Text(MyNumber, "[$-en-GB]#,##0.0#", "it-IT")

CaptureValue2.JPG

Hi Meneghino!

 

I will give it a try this weekend. To see if I can get it working this way. I also will post the solution when I get it working 🙂

 

Thanks for the answer!

 

Best regards

Hi Yme,

 

What is your current situation?

 

If you need any further assistance, please post back.

Regards

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

Hi v-micsh-msft,

 

I have tried it a couple of times. But I do not get it working. I tried to upload images in the post, but apparently this is not possible? Only Url's.

 

So I try to explain: 

I have a database column test with column with type: float.

I have inserted a Form1, which needs input for test

I have created a button which makes a NewForm(Form1);

I have created a button which saves the record, with the value in test: SubmitForm(Form1);

 

When I try to save the input: 11,11 I get the error: The request is invalid. Server answer: Invalid Expression. inner Exception: Token Equal Expected. Start position: (642, 27). End Position (642, 28). inner exception. Token Equal Expected. Start position: (642, 27). End Position (642, 28). inner exception.

 

When I try to save the input 11.11 this is not possible, because the '.' cannot be typed. (which is what I expect, since this is NL-nl).

 

When I try to save the input 11 it saves succesfully.

 

The Format on the DataCardValue1 is: TextFormat.Number. So I expect that I can save the 11,11. Why does this give the exception.

 

I have tried changing the format to Text(Number; ###,##), but no luck unfortunately.

 

Best regards,

 

 

 

 

Community Champion
Community Champion

Hi, I have possibly come across a similar issue:

https://powerusers.microsoft.com/t5/PowerApps-Forum/BUG-Patch-non-integer-to-SQL-server-gives-error-...

 

As a workaround, I created an integer column in the database called amount_x_100 to store the integer amount and then had a calculated column of amount (=amount_x-100/100) to be used elsewhere in the app and database as read-only.

Hi @Yme and @Meneghino,

 

Thanks for the update.

I will collect this from my side and involve the others to take a further investigation on this.

Regards

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

Hi @Yme,

 

Apologize for the late response.

Would you please share the following information to help identify the issue here?

1. Please check your SQL server collation and the install language,

2. If convenient, would you please share a screenshot of the formual that you used in PowerApps which didn't work?

 

Regards

 

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

Helpful resources

Announcements
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (9,086)