Forum Discussion

ElizabethSchlag's avatar
ElizabethSchlag
Qrew Member
9 months ago

Color Coding Fields: Duration

I want to color code a field within a report, but the field is a Duration Type. Currently I am color coding within the report instead of just the field, but it's too much color and doesn't convey what I am needing. I am trying to give a heat map type look for a duration field. Does anyone know how to apply this color coding scheme within this field:

Field Formula: Days In Discovery

Today() - ToDate([Discovery Timestamp])

Color Coding Formula:

var number DaysInDiscovery = ToDays([Days in Discovery]);
 
If(
$DaysInDiscovery >=45 and $DaysInDiscovery <= 60, "#74A235",
$DaysInDiscovery>=61 and $DaysInDiscovery <= 90, "#90B433",
$DaysInDiscovery>=91 and $DaysInDiscovery <= 120, "FFED44",
$DaysInDiscovery>=121 and $DaysInDiscovery <= 180, "F9A900",
$DaysInDiscovery >=181, "FF1100")


------------------------------
Elizabeth Schlagel
------------------------------

13 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    9 months ago

    You will need to create. New formula Rich Text field for your report.  Here re some cheat notes I have.

    Background Shading Yes  
    https://help.quickbase.com/user-assistance/color_field.html

    Background Shading
    var text ColorCode =

    If(

    [Delivery Date]-Today()<Days(1),"pink",

    [Delivery Date]-Today()<Days(3),"yellow",

    [Delivery Date]-Today()<Days(5),"green");

    var text DateText = ToText([Delivery Date]);

    Case($ColorCode,

    "pink","<div style=\"background-color: pink;\"" & ">" &  $DateText & "</div>",

    "yellow","<div style=\"background-color: yellow;\"" & ">" &  $DateText & "</div>",

    "green","<div style=\"background-color: #00C957;\"" & ">" &  $DateText & "</div>", $DateText)


    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • ElizabethSchlag's avatar
      ElizabethSchlag
      Qrew Member
      9 months ago

      Thanks Mark! This is really helpful. I am now receiving an error due to the <div> expecting a different type, but if I remove the ToText to keep the type expectancy, then I get a different error:

      Error: Expecting duration but found text.

      Error: Expecting text but found datetime



      ------------------------------
      Elizabeth Schlagel
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew Champion
        9 months ago

        The field type needs ot be Rich Text, not Duration.



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