Discussions

 View Only
Expand all | Collapse all

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

QuickBaseCoach Dev./Training

QuickBaseCoach Dev./Training07-28-2016 21:25

QuickBaseCoach Dev./Training

QuickBaseCoach Dev./Training07-28-2016 21:48

QuickBaseCoach Dev./Training

QuickBaseCoach Dev./Training07-28-2016 22:53

Archive User

Archive User07-29-2016 14:43

Archive User

Archive User07-29-2016 21:06

James Blackford

James Blackford09-11-2018 18:41

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

     
    Posted 07-28-2016 21:04
    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>")


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

    Posted 07-28-2016 21:16
    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.


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

     
    Posted 07-28-2016 21:22
    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.


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

    Posted 07-28-2016 21:25
    Is the problem that the pennies get truncated if they are zero?


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

     
    Posted 07-28-2016 21:28
    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.


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

    Posted 07-28-2016 21:43
    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]))


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

    Posted 07-28-2016 21:48
    For Percent fields, you need to multiply by 100.


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

     
    Posted 07-28-2016 22:01
    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.


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

     
    Posted 07-28-2016 22:03
    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.


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

     
    Posted 07-28-2016 22:11
    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


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

    Posted 07-28-2016 22:15
    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)


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

     
    Posted 07-28-2016 22:16
    When I add this: &"<div style=\"background-color:red;\">"& [Remaining Hours Original] I get this:

    6.
    6
    0

    Vertical in the field, like that.


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

    Posted 07-28-2016 22:17
    You need to close your DIV element after the field


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

     
    Posted 07-28-2016 22:35
    I still get something like this:

    34
    34
    .0


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

     
    Posted 07-28-2016 22:37
    I need = to be black, over to be red, and under to be green. I don't understand why it's returning multiple values....


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

     
    Posted 07-28-2016 22:39
    And returning 34.0, not this mess:

    34
    34
    .
    340


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

    Posted 07-28-2016 22:53
    Yes,  multiply by 100 in the formula text field.


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

    Posted 07-29-2016 14:43
    Ok, what's the full formula at this point?


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

    Posted 07-29-2016 14:47
    If([Labor Budget Balance Original]<0,"<div style=\"color:red;\">"&"$"&ToFormattedText(Round([Labor Budget Balance Original],0.01),"comma_dot")&"</div>",

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

    [Labor Budget Balance Original]>0,"<div style=\"color:#3EC41C;\">"&"$"&ToFormattedText(Round([Labor Budget Balance Original],0.01),"comma_dot")&"</div>")


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

    Posted 07-29-2016 15:06
    Using that exact formula, but for substituting my own numeric field instead of the [Labor Budget Balance Original] that you have, I received the expected result.


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

    Posted 07-29-2016 16:28
    Yes, sorry I went a different direction. I was trying to get the cell to change colors conditionally, while keeping the correct decimal spaces and $ sign. I still would like to figure that out.


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

    Posted 07-29-2016 16:54
    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>")


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

    Posted 07-29-2016 21:06
    Thank you Blake!


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

    Posted 09-11-2018 18:41
    How to right align the text?