Forum Discussion

KellyLyons1's avatar
KellyLyons1
Qrew Trainee
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 ne...
MarkShnier__You's avatar
MarkShnier__You
Qrew Champion
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 Trainee
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
    Qrew Champion
    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
    ------------------------------
    • KellyLyons1's avatar
      KellyLyons1
      Qrew Trainee
      2 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
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew Champion
        2 years ago
        re: So where would I put the IF statement then?  In the first field?

        No.  Edit the report and at the bottom there is a place to put in a row colorization formula.

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