Format Currency bug?

Peter_Gardner · August 28, 2020, 10:24am

Formatting as currency works as expected for $, but not for any other currency symbol (e.g. £ and €)

See output from small test program below

I can’t see why - so assume it is a bug

Format using $
Format 45.678000 using ‘$#,###.##;($#,###.##);$\n\i\l’ gives result $45.68
Format -45.678000 using ‘$#,###.##;($#,###.##);$\n\i\l’ gives result ($45.68)
Format 0.000000 using ‘$#,###.##;($#,###.##);$\n\i\l’ gives result $nil

Format using £
Format 45.678000 using ‘\£#,###.##;(\£#,###.##);\£\n\i\l’ gives result £45.7
Format -45.678000 using ‘\£#,###.##;(\£#,###.##);\£\n\i\l’ gives result (£45.68
Format 0.000000 using ‘\£#,###.##;(\£#,###.##);\£\n\i\l’ gives result )£nil

Format using €
Format 45.678000 using ‘\€#,###.##;(\€#,###.##);\€\n\i\l’ gives result €46.
Format -45.678000 using ‘\€#,###.##;(\€#,###.##);\€\n\i\l’ gives result (€46.
Format 0.000000 using ‘\€#,###.##;(\€#,###.##);\€\n\i\l’ gives result 0)€nil

the program is

#Pragma Unused args

’ Say hello

Var num As Double
Var formatstring As String
Var resultstring As String

StdOut.WriteLine(“Format using $”)
num = 45.678
formatstring = “$#,###.##;($#,###.##);$\n\i\l”
resultstring = format(num, formatstring)
StdOut.WriteLine(“Format " + num.ToString + " using '” + formatstring + "’ gives result " + resultstring)

num = -45.678
resultstring = format(num, formatstring)
StdOut.WriteLine(“Format " + num.ToString + " using '” + formatstring + "’ gives result " + resultstring)

num = 0
resultstring = format(num, formatstring)
StdOut.WriteLine(“Format " + num.ToString + " using '” + formatstring + “’ gives result " + resultstring)
StdOut.WriteLine(”")

StdOut.WriteLine(“Format using £”)
num = 45.678
formatstring = ReplaceAll(formatstring, “$”, “£”)
resultstring = format(num, formatstring)
StdOut.WriteLine(“Format " + num.ToString + " using '” + formatstring + "’ gives result " + resultstring)

num = -45.678
resultstring = format(num, formatstring)
StdOut.WriteLine(“Format " + num.ToString + " using '” + formatstring + "’ gives result " + resultstring)

num = 0
resultstring = format(num, formatstring)
StdOut.WriteLine(“Format " + num.ToString + " using '” + formatstring + “’ gives result " + resultstring)
StdOut.WriteLine(”")

StdOut.WriteLine(“Format using €”)
num = 45.678
formatstring = ReplaceAll(formatstring, “£”, “€”)
resultstring = format(num, formatstring)
StdOut.WriteLine(“Format " + num.ToString + " using '” + formatstring + "’ gives result " + resultstring)

num = -45.678
resultstring = format(num, formatstring)
StdOut.WriteLine(“Format " + num.ToString + " using '” + formatstring + "’ gives result " + resultstring)

num = 0
resultstring = format(num, formatstring)
StdOut.WriteLine(“Format " + num.ToString + " using '” + formatstring + “’ gives result " + resultstring)
StdOut.WriteLine(”")
’ Quit
Return 0

Sascha_S · August 28, 2020, 10:28am

Which Xojo Version? Maybe you could use Currency.ToText instead?

Because:

The currency symbol is displayed before or after the number as specified by the locale settings.

Rick_Araujo · August 28, 2020, 10:52am

Try “#,##0.00” instead

Rick_Araujo · August 28, 2020, 11:23am

Ok, I noticed the bug. There’s something related to utf-8 processing and string of bytes processing. The chosen chars are multibyte chars and Xojo is doing some bad math behind the scenes and truncating strings. PLEASE, Open a FB Bug report.

Rick_Araujo · August 28, 2020, 11:47am

This is a serious bug with unknown wrong results, even financial losses for organizations. So, please, open a FB Bug report, and Xojo, please, fix it.

Meanwhile, use a single byte workaround like this one:

Var num As Double
Var formatstring As String
Var resultstring As String

formatstring = "\$#,##0.00;(\$#,##0.00);\$\n\i\l"

System.DebugLog("Format using $")
num = 45.678
resultstring = format(num, formatstring)
System.DebugLog("Format " + num.ToString + " Results " + resultstring)

num = -45.678
resultstring = format(num, formatstring)
System.DebugLog("Format " + num.ToString + " Results " + resultstring)

num = 0
resultstring = format(num, formatstring)
System.DebugLog("Format " + num.ToString + " Results " + resultstring)
System.DebugLog("")

System.DebugLog("Format using £")
num = 45.678
resultstring = format(num, formatstring).ReplaceAll("$", "£")
System.DebugLog("Format " + num.ToString + " Results " + resultstring)

num = -45.678
resultstring = format(num, formatstring).ReplaceAll("$", "£")
System.DebugLog("Format " + num.ToString + " Results " + resultstring)

num = 0
resultstring = format(num, formatstring).ReplaceAll("$", "£")
System.DebugLog("Format " + num.ToString + " Results " + resultstring)
System.DebugLog("")

System.DebugLog("Format using €")
num = 45.678
resultstring = format(num, formatstring).ReplaceAll("$", "€")
System.DebugLog("Format " + num.ToString + " Results " + resultstring)

num = -45.678
resultstring = format(num, formatstring).ReplaceAll("$", "€")
System.DebugLog("Format " + num.ToString + " Results " + resultstring)

num = 0
resultstring = format(num, formatstring).ReplaceAll("$", "€")
System.DebugLog("Format " + num.ToString + " Results " + resultstring)
System.DebugLog("")
Quit

Peter_Gardner · August 28, 2020, 12:57pm

Thanks, but two problems with that. First there is no control over the actual format and second getting different currencies in the same app would be really horrendous.

Peter_Gardner · August 28, 2020, 1:13pm

Thanks for the confirmation of the bug. I have now submitted a bug report on FB

Rick_Araujo · August 28, 2020, 1:19pm

Publish the number here, so we can follow up things.

TimStreater · August 28, 2020, 1:59pm

What happens if you use num.ToString (locale, formatstring) instead of format() ?

Peter_Gardner · August 28, 2020, 2:23pm

Bug report number is 61522

Rick_Araujo · August 28, 2020, 2:36pm

Even worse. “Crash”. Causes an unknown exception saying “Bad Format” for something Format() works perfectly.

Seems it does not accept 3 format parameters, if we remove the “zero” format it does not crash anymore. Another BUG.

Peter_Gardner · August 28, 2020, 2:44pm

Using num.ToString (locale, formatstring)

Gives 45.678 -45.67 and 0 instead of £45.67 (£45.67) and £nil

So at least not so much garbage, but ignores the formatting and the currency symbols altogether. (I set the local to UK, US and FR and got the same result each time)

Rick_Araujo · August 28, 2020, 2:45pm

In my locale, under Windows, it crashes if using the ZERO format part.

Peter_Gardner · August 28, 2020, 2:46pm

I’m on Mac. 2019r3.1

I’m just about to try downloading 2020r1 (or is that a bad idea?)

Rick_Araujo · August 28, 2020, 2:47pm

I was testing in the 2020R1. Download it and do a test there. It does not hurt having it and not using in production.

Valdemar_De_SOUSA · August 28, 2020, 2:52pm

I use this :

 Public Function ToCurrency(Extends value As String) as Currency
  
  Dim userValue As Text
  userValue = value.ToText
  
  Dim myvalue As Currency
  Var FRLocale As new Xojo.Core.Locale("fr-FR")
  
  if FRLocale.DecimalSeparator = "," then
    dim value2 As String = value.ReplaceAll(","," ")
    value2 = value2.ReplaceAll(".",",")
    value = value2
  end if
  
  myvalue = Currency.FromText(value.ToText, FRLocale)
  
  Return myvalue
End Function

Rick_Araujo · August 28, 2020, 2:55pm

I also have my own format helpers, but right now we are tracking bugs in the native functions here.

Rick_Araujo · August 28, 2020, 3:43pm

Tests (Xojo 2020R1 Windows)

// ASCII works
System.DebugLog(Format(45.678, "\$#,##0.00;(\$#,##0.00);0.00\!"))
System.DebugLog(Format(-45.678, "\$#,##0.00;(\$#,##0.00);0.00\!"))
System.DebugLog(Format(0, "\$#,##0.00;(\$#,##0.00);0.00\!"))

//UTF8 multibytes chars causes defects
System.DebugLog(Format(45.678, "\€#,##0.00;(\€#,##0.00);0.00\!"))
System.DebugLog(Format(-45.678, "\€#,##0.00;(\€#,##0.00);0.00\!"))
System.DebugLog(Format(0, "\€#,##0.00;(\€#,##0.00);0.00\!"))

//New API2
Var d As Double // Because Double(45.678).ToString says "Type mismatch error. Expected Double, but got Double"
d = 45.678
System.DebugLog(d.ToString("\$#,##0.00;(\$#,##0.00)")) // needed to remove the 3rd parameter or crash ocurred
d = -45.678
System.DebugLog(d.ToString("\$#,##0.00;(\$#,##0.00)"))
d = 45.678
System.DebugLog(d.ToString(Locale.Current, "\$#,##0.00;(\$#,##0.00)")) // needed to remove the 3rd parameter or crash ocurred
d = -45.678
System.DebugLog(d.ToString(Locale.Current, "\$#,##0.00;(\$#,##0.00)"))
d = 0
System.DebugLog(d.ToString("\$#,##0.00;(\$#,##0.00);0.00\!")) // Crash due to existence of the 3rd parameter

Quit

Results:

       $45,68
       ($45,68)
       0,00!

       €46,
       0(€46
       00)0,00!

       \$45.68
       (\$45.68)
       \$45,68
       (\$45,68)
       Crash (Exception)

TimStreater · August 28, 2020, 4:01pm

This is what I tried:

TextArea1.AppendText ("Format using .ToString" + EndOfLine)
num = 45.678
resultstring = num.ToString (locale.Current, "¤#,##0.00;(¤#,##0.00)")
TextArea1.AppendText ("Format " + num.ToString + " Results " + resultstring + EndOfLine)

num = -45.678
resultstring = num.ToString (locale.Current, "¤#,##0.00;(¤#,##0.00)")
TextArea1.AppendText ("Format " + num.ToString + " Results " + resultstring + EndOfLine)

num = 0
resultstring = num.ToString (locale.Current, "¤#,##0.00;(¤#,##0.00)")
TextArea1.AppendText ("Format " + num.ToString + " Results " + resultstring + EndOfLine)

You don’t need the backslash (\). Just put your currency symbol there.
If you use the ¤ then you get the currency symbol for your locale.
You are right that there seems to be no third parameter for zero. But I didn’t get an exception when I added one, it just ignored the whole format string.
The £ in UTF-8 is two bytes, the € is three bytes. So it looks like the format string in format() MUST be ASCII.

Norman_Palardy · August 28, 2020, 4:03pm

Seems a bug for a language that uses UTF-8 natively for strings