Forum Discussion

IanIan's avatar
IanIan
Qrew Member
9 years ago

Conditionally change color of formula numeric field, either text or background, without using a a Formula Text field.

I want to change either the background or text of a formula numeric field based on conditions (e.g. over budget). I have made it work using a formula text field that mimics the number, and even got a "$" or "%" to work.

It seems hit or miss, and sometimes I get weird numbers. Is there a way to directly change the color of a numeric field? Here is an example of a formula:



If([Remaining Project Budget Funds Original]<0,"<div style=\"color:red;\">"&"$"&ToFormattedText(Round([Remaining Project Budget Funds Original],0.01),"comma_dot")&"</div>",

[Remaining Project Budget Funds Original]=0,"<div style=\"color:black;\">"&"$"&ToFormattedText(Round([Remaining Project Budget Funds Original],0.01),"comma_dot")&"</div>",

[Remaining Project Budget Funds Original]>0,"<div style=\"color:#3EC41C;\">"&"$"&ToFormattedText(Round([Remaining Project Budget Funds Original],0.01),"comma_dot")&"</div>")
Formulas and functions
reports and charts
Tables and fields

23 Replies

Sort By
Show More
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    9 years ago
    Ok, here are the two options I came up with.

    For coloring the Font Only:

    var text tnum = ToText(Int(Abs([Numeric]))) & "." &Right(ToText(Frac(Abs([Numeric]))*100+100),2);

    If([Numeric]<0,"<div style=\"color:red;\">"&"$ "& $tnum&"</div>",

    [Numeric]=0,"<div style=\"color:black;\">"&"$ "&$tnum&"</div>",

    [Numeric]>0,"<div style=\"color:#3EC41C;\">"&"$ "&$tnum&"</div>")

    For coloring the Background & making the Font white:

    var text tnum = ToText(Int(Abs([Numeric]))) & "." &Right(ToText(Frac(Abs([Numeric]))*100+100),2);

    If([Numeric]<0,"<div style=\"background-color:red;color:white;\">"&"$ "& $tnum&"</div>",

    [Numeric]=0,"<div style=\"background-color:black;color:white;\">"&"$ "&$tnum&"</div>",

    [Numeric]>0,"<div style=\"background-color:#3EC41C;color:white;\">"&"$ "&$tnum&"</div>")