Format decimal and float numbers

[Yme]

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.

[v-micsh-msft]

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

[Meneghino]

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.

[Meneghino]

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")

[Yme]

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

[v-micsh-msft]

Hi Yme,

What is your current situation?

If you need any further assistance, please post back.

Regards

[Yme]

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]

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.

[v-micsh-msft]

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

[v-micsh-msft]

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