Forum Discussion

KellyLyons1's avatar
KellyLyons1
Qrew Cadet
2 years ago

Highlight blank values in a formula field help

I have a "paid date" field that I have converted to text field in order to use a formula to display the paid date in a using 2 different formula fields (for options to group/filter the report)

I need to highlight the fields that are blank in both reports.  I am using the Case function in both fields and I think I need to add If-then as well?  Any ideas how to get this to work?



------------------------------
Kelly Lyons
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    2 years ago
    Can you post your actual code with Copy Paste.  It's too difficult to suggest edits to screen shots.

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • KellyLyons1's avatar
      KellyLyons1
      Qrew Cadet
      2 years ago
      Sure sorry about that:

      1st formula field:

      "<div style = 'color: #FFFFFF; text-align: center; line-height: 1.25em; width: 75px; font-weight: bold; vertical-align: middle; background: "&

      Case([PAID DATE - text],
      "01-27-2017", "#bfef45",
      "04-18-2017", "#ffe119",
      "05-23-2017", "#ce97eb",

      "01-30-2020", "#DFFF00",
      "02-21-2020", "#6495ED",
      "05-06-2020", "#FF7F50",
      "06-18-2020", "#40E0D0",
      "07-30-2020", "#DE3163",
      "08-27-2020", "#3F33FF",
      "09-17-2020", "#64103F",
      "10-20-2020", "#095C3B",
      "11-24-2020", "#F730BB",
      "12-30-2020", "#F7960A")

      &"; border-radius: 15px'>" & [PAID DATE - text] & "</div>"

      My second formula field to group dates in HTML

      Case([PAID DATE - text],
      "01-27-2017", "<font color='#FD8940' size='3'>Paid 1-27-2017</font>",
      "05-23-2017", "<font color='#F26641' size='3'>Paid 5.23.2017</font>",
      "04-18-2017", "<font color='#89C765' size='3'>Paid 4.18.2017</font>",

      "01-30-2020", "<font color='#ff0033' size='3'>Paid 1.30.2020</font>",
      "02-21-2020", "<font color='#00cc00' size='3'>Paid 2.21.2020</font>",
      "05-06-2020", "<font color='#ff0033' size='3'>Paid 5.06.2020</font>",
      "06-18-2020", "<font color='#9669fe' size='3'>Paid 6.18--2020</font>",
      "07-30-2020", "<font color='#ff6600' size='3'>Paid 7-30-2020</font>",
      "08-27-2020", "<font color='#2897b7' size='3'>Paid 8-27-2020</font>",
      "09-17-2020", "<font color='#cc0099' size='3'>Paid 9-17-2020</font>",
      "10-20-2020", "<font color='#9900ff' size='3'>Paid 10-20-2020</font>",
      "11-24-2020", "<font color='#f26641' size='3'>Paid 11-24-2020</font>",
      "12-30-2020", "<font color='#89C765' size='3'>Paid 12-30-2020</font>")
      ​

      ------------------------------
      Kelly Lyons
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        2 years ago
        We can mess around with the formula but why not do regular row colorization for the blank rows and leave that fancy formula alone.

        IF(IsNull([Paid Date], "yellow")

        btw, hard coding dates is generally not a great practice as it will need ongoing maintenance, but maybe you have a special situation where it makes sense.

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