Answered

Format numbers, and regional problems?

(0) Share

Report

Posted on by TerjeF

108

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?

Categories:

General Questions

All responses (6)

Answers (1)

NPrice99 1,282 on at

Like (0)

Report

Re: Format numbers, and regional problems?

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
Steelman70 873 on at

Like (1)

Report
Re: Format numbers, and regional problems?

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-p/2268

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
TerjeF 108 on at

Like (0)

Report

Re: Format numbers, and regional problems?

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!
Brian Dang 3,976 on at

Like (1)

Report

Re: Format numbers, and regional problems?

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.
VinnyClegg 66 on at

Like (1)

Report
Re: Format numbers, and regional problems?

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)
Verified answer

Steelman70 873 on at

Like (4)

Report

Re: Format numbers, and regional problems?

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