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
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
      ------------------------------
      • 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
        ------------------------------