Format numbers, and regional problems?

[TerjeF]

Hi

I've connected to SharePoint list, and a field for calculated values (multiplying two cells), is showing in PowerPoint with ten digits....

I've tried text("Totalprice,"#.#") and text("Totalprice,"#.##) etc.... - but same same....

Shows as 32640.0000000000  etc.....

Any good ideas? Can it be related to Norwegian settings/default formatting?

[Steelman70 · Accepted solution]

Hello, it is not a regional problem.  It is really strange, but this is what works for me:

TextBoxB.Text = Text(Value(TextBoxA.Text), "#.#") where TextBoxA contains the number to be formatted

Let me clarify with an example, screenshot below:

1) Create a new blank PowerApp

2) Add four text boxes, TextBox1, TextBox2 etc.

3) Place any two numbers as the Text of TextBox1 and TextBox2, for example 123456 and 123

4) Make the Text of TextBox3 be TextBox1/TextBox2 and it should now show 1003.70731707

5) Make the Text of TextBox4 be Text(TextBox3, "#.#") and it will show 1003.70731707 and that reproduces your issue

Now for the trick...

6) Change the Text of TextBox4 to Text(Value(TextBox3.Text), "#.#") and it should now show 1003.7 and voila!

Please mark as answer if it helps.

I think that basically all text boxes contain text (and confusingly you can enter 123 as 123 or "123" and it doesn't seem to make any difference.  "123" is entered automatically if you enter the number in the text box itself and you can get 123 by editing the Text property)  I am not sure how to access the value directly.  But if that is the case then TextBox1/TextBox2 should also not work.  Any clarifications from the PowerApps team would be welcome

 

[VinnyClegg]

You can always use round()

I have a similar function to calculate a pro rata salary that is:

Round((Sum((TextInput5/TextInput1)*TextInput3)), "£#,###.00" ),2)

[mr-dang]

Steelman got it right. 

Here's another example. If you are trying to Patch numerical data back into your datasource, Patch will take text by default if you have not setup your column for numerical values.

So once you try to recall the data you patched, you need to convert those numbers into values, and thereafter use Text to format it with the decimals you want.

[TerjeF]

Great, thanks. I wasn't able to format this in the textbox itself, but when I made a new textbox, i was able to format the new textbox (that only shows the value of the original one) as Intended. ANd then I made the original textbox unvisable. SO, problem solved, but I think the PowerApp team needs to fix this issue...  🙂

THanks for the answare!  🙂 great forum!

[Steelman70]

Actually I have played around a bit and i think I now understand what is going on.  The issue is all about typing, and PowerApps formulas seem to be in the weakly typed camp, see Wikipedia for background:

https://en.wikipedia.org/wiki/Strong_and_weak_typing

In regards to using numbers from SharePoint lists I have published this post in this forum:
https://powerusers.microsoft.com/t5/PowerApps-Forum/Using-numbers-and-dates-from-SharePoint-lists/td...

The values of number columns in SharePoint are returned correctly as numbers, so that is not the issue in your case, Terje.

The real issue is that when you use the value of a text box (i.e. its .text property), then this value is a value of text type.  This is even in case the text box has a numerical formula as its text value.  In other words, the "value" of all text boxes are of the text type, which is obvious once you think about it, but a bit counterintuitive if generated by a formula.

So, Terje, if you force type conversion to a number in your hidden text box, then you should be able to use the Text function directly without the extra text box.  Here is why and how.

The problem that Terje is seeing is because of the way the Text(value_par, format_string) function of PowerApps works.

• If value_par is of the number type (e.g. the value directly returned from a SharePoint number column or a numerical formula) then it will be formatted correctly
• If value_par is of the text type (e.g. refers to the value in another text box) then format_string is disregarded and value_par is returned unformatted (no error is returned, which is why I talk about weak typing).  If value_par is of the text type but is a number, then you can force the type conversion to number type by using value*1 or value+0 as value_par (I used a similar trick with Access Web Apps occasionally).   You can also explicity use Value(value_par) to convert value_par to a number, and I actually prefer this solution

Please let me know if this does not work or if you have suggestions how to make it clearer

[NigelP]

Hi

I am in the UK and I am trying to display a field as £###,###.

I have tried Text(Value(ThisItem.CAPEX,"£###,###")) and I get an error Language code 'en-US[£-en-GB not supported.

I have also tried Text(ThisItem.CAPEX,[£-en-GB]"£###,###") and I get the same error.

The value is coming from a SharePointListitem of type cyrrency.

What iswrong

Thanks

Nigel