cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Yme
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

Re: Format decimal and float numbers

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.
Meneghino Community Champion
Community Champion

Re: Format decimal and float numbers

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

Meneghino Community Champion
Community Champion

Re: Format decimal and float numbers

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

Yme
New Member

Re: Format decimal and float numbers

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

Community Support
Community Support

Re: Format decimal and float numbers

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.
Yme
New Member

Re: Format decimal and float numbers

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,

 

 

 

 

Meneghino Community Champion
Community Champion

Re: Format decimal and float numbers

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.

Community Support
Community Support

Re: Format decimal and float numbers

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.
Community Support
Community Support

Re: Format decimal and float numbers

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
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Top Solution Authors
Top Kudoed Authors
Users online (5,164)