Forum Discussion

AmyGosz2's avatar
AmyGosz2
Qrew Cadet
6 years ago

Formula-Rich Text field dropping the currency setting from the formula field

I have a Formula Numeric field to create a 'total'. I want to take this 'total' and display it with a colored background. I set up a Formula-Rich Text field to do this. However, the Rich Text field is dropping the 2 decimal places. For example: 1000.00 displays as 1000, 337.50 displays as 337.5. 

Here is the formula I am using:
 "<div style=\"background-color:pink;\">"& [Annual Surcharge Total] &"</div>"

How can I get the Formula Rich Text field to display 2 decimal points?
Formulas and functions
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    try this

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


     "<div style=\"background-color:pink;\">"& $FormattedNumber  &"</div>"


  • AmyGosz2's avatar
    AmyGosz2
    Qrew Cadet
    6 years ago

    When I put the code in, the [Annual Surcharge Total] field is highlighted in yellow with the reason "Expecting text but found number". Do I need to convert that value to a 'text' field and use the new text field? Or, change the word 'text' in the formula to 'number'?
  • AmyGosz2's avatar
    AmyGosz2
    Qrew Cadet
    6 years ago

    THAT WORKED!! I also changed all the [Price] field names to the actual field name.
    This is the final code:

    var number Price = [Annual Surcharge Total] ;
    var text FormattedNumber =
    "$" &
    ToFormattedText([Annual Surcharge Total],"comma_dot",3) &
    If(
      not Contains(ToText([Annual Surcharge Total]),"."),
      ".00",
      Length(Right(ToText([Annual Surcharge Total]),"."))=1,"0"
    );


     "<div style=\"background-color:pink;\">"& $FormattedNumber  &"</div>"


    Thank you so much for the quick response!

  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    OK np,

    The way you did it, then you do not need this line.

    var number Price = [Annual Surcharge Total] ;

  • AmyGosz2's avatar
    AmyGosz2
    Qrew Cadet
    6 years ago
    Ok. The reason I did change that is because the [Price] field was highlighted in yellow so I knew it had to be changed to something.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    You can round your value before applying the formula.  If you post your formula I can help edit it. 
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    .. actually it will be more complicated than that but I can still try to help if you post your current formula.
  • JamesBlackford's avatar
    JamesBlackford
    Qrew Member
    6 years ago
    Would like this formula to show whole dollars without the decimals.  Output should show something like:  $1,234 or $12,456 or $564

    Thx


    var text FormattedNumber =
    "$" & 
    ToFormattedText([Annual Surcharge Total],"comma_dot",3) & 
    If(
      not Contains(ToText([Annual Surcharge Total]),"."),
      ".00",
      Length(Right(ToText([Annual Surcharge Total]),"."))=1,"0"
    );