Forum Discussion

JackWoods's avatar
JackWoods
Qrew Trainee
4 years ago

Rich Text Formula Field - Comma for Currency #?

Hey Community, I'm using a Rich Text Formula field called NPV(Color) to show the NPV value in red when it's negative. It's working well but now I just need to format it - I can't seem to add commas...
Formulas and functions
MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
4 years ago
try this

var text Price = [NPV];
var text Value = 

"$" &
ToFormattedText([Price],"comma_dot",3) &
If(
not Contains(ToText([Price]),"."),
".00",
Length(Right(ToText([Price]),"."))=1,"0"
);

If([NPV]<=0,"<font color=\"red\">$" & $Value & "</font>","<font color=\"black\">$" & $Value &"</font>")​


------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
  • JackWoods's avatar
    JackWoods
    Qrew Trainee
    4 years ago
    I understand the format and know what you're getting at. I'm not sure if I put it in correct. 

    It tells me I have additional characters past my formula...

    Please see screen grab is that right?

    ------------------------------
    Jack Woods | Low Code 4 Lyfe
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      4 years ago
      Please copy and paste your code, so I can see it all and edit it.
      Also please post the error message when you save.

      ------------------------------
      Mark Shnier (YQC)
      Quick Base Solution Provider
      Your Quick Base Coach
      http://QuickBaseCoach.com
      mark.shnier@gmail.com
      ------------------------------
      • JackWoods's avatar
        JackWoods
        Qrew Trainee
        4 years ago
        ERROR MESSAGE:

        There are extra characters beyond the end of the formula.

        "$" &
        ToFormattedText([NPV],"comma_dot",3) &
        If(
        not Contains(ToText([NPV]),"."),
        ".00",
        Length(Right(ToText([NPV]),"."))=1,"0"
        )  ;

        If([NPV]<=0,"<font color=\"red\">$" & $Value & "</font>","<font color=\"black\">$" & $Value &"</font>");


        FORMULA:

        "$" &
        ToFormattedText([NPV],"comma_dot",3) &
        If(
        not Contains(ToText([NPV]),"."),
        ".00",
        Length(Right(ToText([NPV]),"."))=1,"0"
        );

        If([NPV]<=0,"<font color=\"red\">$" & $Value & "</font>","<font color=\"black\">$" & $Value &"</font>");


        ------------------------------
        Jack Woods | Low Code 4 Lyfe
        ------------------------------