Discussions

 View Only
  • 1.  Highlight blank values in a formula field help

    Posted 22 days ago
    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
    ------------------------------


  • 2.  RE: Highlight blank values in a formula field help

    Posted 22 days 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
    ------------------------------



  • 3.  RE: Highlight blank values in a formula field help

    Posted 22 days 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
    ------------------------------



  • 4.  RE: Highlight blank values in a formula field help

    Posted 22 days 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
    ------------------------------



  • 5.  RE: Highlight blank values in a formula field help

    Posted 22 days 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
    ------------------------------



  • 6.  RE: Highlight blank values in a formula field help

    Posted 22 days 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
    ------------------------------