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
------------------------------
  • 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
      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
        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
        ------------------------------