Forum Discussion

KellyLyons1's avatar
KellyLyons1
Qrew Cadet
3 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 ne...
MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
3 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
    3 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
      3 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
      ------------------------------
      • KellyLyons1's avatar
        KellyLyons1
        Qrew Cadet
        3 years ago
        Hi Mark!

        So where would I put the IF statement then?  In the first field?

        Yes, I am only using the formatting to finish up a project, it was the fastest way I could think of that I knew a bit about!

        I actually was using the 1st field to try and highlight blank/null fields and ended up with trying the HTML as well.

        I prefer the 1st field.

        ------------------------------
        Kelly Lyons
        ------------------------------