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
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
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      4 years ago
      Can you use the whole formula I posted?  You left out part of it.  But in any event here is a better version to use.


      var text Value = 

      "$" &
      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>")​




      ------------------------------
      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
        Thank you - that worked. sorry for the confusion.

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