Forum Discussion

PaigeMann's avatar
PaigeMann
Qrew Member
2 years ago

Color Coding Fields

I am trying to make a report that color codes the individual fields based on a given specification range. For example, the one I'm starting with I want to highlight the field in red when the number is greater than 1.50. I tried looking through the community tab to see where in the coding I went wrong but I wasn't having any luck. For some reason, the code is functioning properly in terms of the highlighting, but isn't pulling the number over correctly. Here is the code that I have set up in the Formula Rich Field (I've also attached an image):

If([Lot Sensory Score:] > 1.5, "<div style=\"background-color:red;\">[Lot Sensory Score:]\n</div>", ToText([Lot Sensory Score:]))

For some reason the second "[Lot Sensory Score]" isn't pulling correctly and isn't turning green in the code window.

Eventually, I want to get more complicated with the color coding fields (i.e. if this field is A, color based on 80-100; if this field is B...), but I was trying to start simple.

------------------------------
Paige M.
------------------------------
Formulas and functions

3 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew #1 Challenger
    2 years ago
    Try this
    If([Lot Sensory Score:] > 1.5,
    "<div style=background-color:red>" & ToText([Lot Sensory Score:]) & "</div>",
    ToText([Lot Sensory Score:]))

     if you want to have more complex colors you can do this.

    If(
    [Lot Sensory Score:] > 1.5,
    "<div style=background-color:red>" & ToText([Lot Sensory Score:]) & "</div>",

    [Lot Sensory Score:] > 1.2,
    "<div style=background-color:Blue>" & ToText([Lot Sensory Score:]) & "</div>",

    [Lot Sensory Score:] > 1.0,
    "<div style=background-color:yellow>" & ToText([Lot Sensory Score:]) & "</div>",

    ToText([Lot Sensory Score:]))

    There is also complete set of hex color codes here https://laurahillier.quickbase.com/db/bhy8pumuk?a=q&qid=-1000003&dr=1 which you can use instead of the color names in words.


    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • PaigeMann's avatar
      PaigeMann
      Qrew Member
      2 years ago
      Thank you Mark. That ended up working to solve my issue. I figured that I had something in the base coding that was throwing it off.

      For the complex colors, if I were to tie in a specific field reference and the number range, would that be an additional and statement within the if statement?

      For example:

      Field 1 = Type (i.e. A, B, C, etc.)
      Field 2 = Sensory Score (the measured number)

      Type A has a range of 1 - 1.2; Type B has a range of 1.2 - 1.5; etc.

      Would the code look something like this:

      If(
      [Field 1] = C & [Field 2] > 1.5,
      "<div style=background-color:Red>" & ToText([Field 2]) & "</div>",

      [Field 1] = B & [Field 2] > 1.2,
      "<div style=background-color:Blue>" & ToText([Field 2]) & "</div>",

      [Field 1] = A & [Field 2] > 1.0,
      "<div style=background-color:Yellow>" & ToText([Field 2]) & "</div>",

      ToText([Field 2]))


      ------------------------------
      Paige Mann
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew #1 Challenger
        2 years ago
        ... you were close.  & means concatenation, similar to excel and AND means boolean 

        If(
        [Field 1] = C and [Field 2] > 1.5,
        "<div style=background-color:Red>" & ToText([Field 2]) & "</div>",

        [Field 1] = B and [Field 2] > 1.2,
        "<div style=background-color:Blue>" & ToText([Field 2]) & "</div>",

        [Field 1] = A and [Field 2] > 1.0,
        "<div style=background-color:Yellow>" & ToText([Field 2]) & "</div>",

        ToText([Field 2]))

        ------------------------------
        Mark Shnier (Your Quickbase Coach)
        mark.shnier@gmail.com
        ------------------------------