Discussions

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

    Posted 01-16-2023 13:38
    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 01-16-2023 16:17
    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 01-16-2023 16:32
    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 01-16-2023 16:38
    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 01-16-2023 16:45
    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 01-16-2023 16:47
    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
    ------------------------------