Skip to main content
Question

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

  • June 20, 2019
  • 0 replies
  • 85 views
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?
    This topic has been closed for replies.

    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
    • Author
    • Registered
    • June 21, 2019

    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'?
    Sorry

    Make this change

    var number Price = [Annual Surcharge Total] ;
    • AmyGosz2
    • Author
    • Registered
    • June 21, 2019

    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!

    OK np,

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

    var number Price = [Annual Surcharge Total] ;

    • AmyGosz2
    • Author
    • Registered
    • June 21, 2019
    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.
    How to show whole dollars only with comma's?  $1,234 or $568 or $100,000
    You can round your value before applying the formula.  If you post your formula I can help edit it. 
    .. actually it will be more complicated than that but I can still try to help if you post your current formula.
    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"
    );
    This tested OK to do background shading and drop the decimals

    var number Price = Round([Annual Surcharge Total]) ;
    var text FormattedNumber =
    "$" & 
    ToFormattedText($Price,"comma_dot",3); 

     "<div style=\"background-color:pink;\">"& $FormattedNumber  &"</div>"
    Works great!  Thx
    Terms & ConditionsAccessibility statement