Forum Discussion

GB_DirectorOfOn's avatar
GB_DirectorOfOn
Qrew Trainee
12 years ago

Keeping (or adding) decimal places when converting number to text...

I'm working with multiple currencies and trying to combine them into a single field (I realize this won't allow me to have a total due to the fact they're converting to text in order to maintain currency symbol), but I can't find a way to make everything go to 2 decimal places. Whole numbers appear as whole numbers, while decimal places are displaying as many as 4. My formula is below... any help I could get would be appreciated...

If([Client - Address: Country2]="United Kingdom", List("", "?", ToFormattedText([CC Proposed - GBP],"comma_dot")), [Client - Address: Country2]="United States", List("", "$", ToFormattedText([CC Proposed - USD],"comma_dot")), [Client - Address: Country2]="France", List("", "?", ToFormattedText([CC Proposed - EUR],"comma_dot")),null)

Formulas and functions
Tables and fields
  • KenFirchKenFirc's avatar
    KenFirchKenFirc
    Qrew Trainee
    12 years ago
    Too bad the new ToFormattedText function is weak and can't handle this basic situation! Maybe there are more parameters to the ToFormattedText function we don't know about. Probably not the most efficient, and there's got to be a better way. Not sure how it will work under various regional settings, but....

    var number v = Case(Trim([Client - Address: Country2]),
    "United Kingdom", [CC Proposed - GBP],
    "United States", [CC Proposed - USD],
    "France", [CC Proposed - EUR],
    [CC Proposed - GBP]);
    var text currencysymbol = Case(Trim([Client - Address: Country2]),
    "United Kingdom", "� ",
    "United States", "$ ",
    "France", "� ",
    "� ");
    var text posnegsign = If($v < 0,"-","");
    var text inttext = ToText(Abs(Int($v)));
    var text fractext = ToText(Abs(Round(Frac($v),0.01)));
    var number fractextlength = Length(ToText(Abs(Round(Frac($v),0.01))));

    $currencysymbol & $posnegsign & $inttext & Case($fractextlength,
    1, ".00",
    3, Mid($fractext,2,2) & "0",
    4, Mid($fractext,2,3))
  • GB_DirectorOfOn's avatar
    GB_DirectorOfOn
    Qrew Trainee
    12 years ago
    Thanks for your help. I ended up doing what is probably the most basic solution, block non-local currencies at the role level. Note ideal, but it will get the job done in terms of showing in the correct currency as well as being able to total etc.
  • UNISBOT's avatar
    UNISBOT
    Qrew Member
    8 years ago
    This hasn't been updated in a while, but I was able to create a simple formula solution for this issue. 

    Part(ToText([Discount Total]),1,".")&"."&Left(Part(ToText([Discount Total]),-1,"."), 2)