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
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    9 years ago
    The short answer is no. A formula numeric has no option for inserting HTML. The only way to do that would be to use the Text Formula with HTML enabled as you mentioned.
  • IanIan's avatar
    IanIan
    Qrew Member
    9 years ago
    Thanks Blake. What would the formula be to conditionally change a related formula field? For example, if the project is over budget, have a [Budget Status] field change red.
  • IanIan's avatar
    IanIan
    Qrew Member
    9 years ago
    I am trying to do this with percents, remaining hours, and budget. Yes, sometimes there is a truncation issue. Sometimes I get a "1.03" instead of a "103" in the formula text field. If I add 100 to the source field, it messes up the original calculation.
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    9 years ago
    It would be done in the same way that you outlined in your example, except that the value presented would be a Text value. To change the background, this is the example (albiet with numeric values) that I've used:

    If(Round([numeric])<[numeric goal],"<div style=\"background-color:pink;width:50\">"&ToText(Round([numeric]))&"</div>",ToText(Round([numeric]))
  • IanIan's avatar
    IanIan
    Qrew Member
    9 years ago
    If I'm converting numeric to text such as 6.0, what do I add to the formula to keep that decimal place? Right now it converts on to 6.
  • IanIan's avatar
    IanIan
    Qrew Member
    9 years ago
    Right, but can I multiply by 100 in the formula text field? If I multiply by 100 in the formula numeric field it obviously screws that up.
  • IanIan's avatar
    IanIan
    Qrew Member
    9 years ago
    OK, I got this to work from an earlier post from Mark.

    If([Remaining Hours Original] >0,
    ToText(Int([Remaining Hours Original]))
    & "."
    & If(Int([Remaining Hours Original])=[Remaining Hours Original],"0",
         Length(Right(ToText([Remaining Hours Original]),"."))=1,Right(ToText(Frac([Remaining Hours Original])),1) & "0",
         Right(ToText(Frac([Remaining Hours Original])),".")))

    I also want to conditionally change the color. How do I add that? thanks
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    9 years ago
    You'll actually have to break apart the value into the Integer and the Remainder and do something along these lines:

    Right(ToText(Frac[Numeric]*100+100),2)