Discussions

Expand all | Collapse all

Color-Coding Cells based on Dates AND Multi-Choice

  • 1.  Color-Coding Cells based on Dates AND Multi-Choice

    Posted 08-23-2021 14:48

    I have a multi-choice field that you can do one of three things with. 1) Leave it blank 2) Select "Missed" 3) Select "Scheduled"

    I want the field next to it to color-code base on the info. 

    1) If left blank I want the cell to turn red if a date is past and yellow if the date is upcoming

    2) MIssed should be gray

    3) Scheduled should be green

    I already have a formula that almost does what, except originally we have a check box that was either checked or not, and now we have a multi-choice. COuld someone help me update this with language for the multi-choice field? 

    Here is the old code: 

    var text red = "<a style=\" text-decoration: none; background: #ea4335; border-radius: 5px; color: #fff; display: inline-block; width: 160px; text-align: center; padding: 8px 20px; font: normal 700 14px/1 \"Calibri\", sans-serif; text-shadow: none; \">"&ToText(ToDays(Today()-[Appointment Needed By]))&" Days Overdue</a>";

    var text green = "<a style=\" text-decoration: none; background: #34a853; border-radius: 5px; color: #fff; display: inline-block; width: 160px; text-align: center; padding: 8px 20px; font: normal 700 14px/1 \"Calibri\", sans-serif; text-shadow: none; \">Complete</a>";

    var text yellow = "<a style=\" text-decoration: none; background: #fbbc05; border-radius: 5px; color: #fff; display: inline-block; width: 160px; text-align: center; padding: 8px 20px; font: normal 700 14px/1 \"Calibri\", sans-serif; text-shadow: none; \">"&ToDays(Today()-[Appointment Needed By])&" Days Due</a>";

    If([Scheduled - Yes]=true,$green,
    If([Scheduled - Yes]=false and [Appointment Needed By] >= Today(),$yellow,
    If([Scheduled - Yes]=false and [Appointment Needed By] < Today(),$red)))

    ____________

    EXAMPLE: Marie Lee should be Gray "Missed"



    ------------------------------
    Meagan McOlin
    ------------------------------



  • 2.  RE: Color-Coding Cells based on Dates AND Multi-Choice

    Posted 08-23-2021 15:21
    Edited by Austin K 08-23-2021 15:21
    Your formula works from the top down. Once it finds the first thing in that formula that matches it ignores the rest of it. 

    If(
    [Scheduled - Yes] = "Scheduled" ,$green,
    [Scheduled - Yes] = "Missed" ,$gray,
    [Scheduled - Yes] = "" and [Appointment Needed By] >= Today(),$yellow,
    [Scheduled - Yes] = "" and [Appointment Needed By] < Today(),$red
    )

    This would color anything with Scheduled selected green, ignoring any other thing on the record. Gray if Missed is selected, and then if blank would color either yellow or red. Let me know if it does not work as anticipated but I think it should. 

    Although now that I look at your post you have given your old formula so this may not help you at all since you are asking how to update a new formula that is not here.


  • 3.  RE: Color-Coding Cells based on Dates AND Multi-Choice

    Posted 08-24-2021 12:07

    If anyone is interested I found a code that works for me.

    Old (checkbox version):

    If([Scheduled - Yes]=true,$green,
    If([Scheduled - Yes]=false and [Appointment Needed By] >= Today(),$yellow,
    If([Scheduled - Yes]=false and [Appointment Needed By] < Today(),$red)))

    New (multi choice version):

    If([Scheduled - Yes]="Scheduled", $green,
    If(([Scheduled - Yes]="Missed"), $gray,
    If(Contains([Scheduled - Yes],"Unscheduled") and [Appointment Needed By] >= Today(),$yellow,
    If(Contains([Scheduled - Yes],"Unscheduled") and [Appointment Needed By] < Today(),$red))))

    Thanks!



    ------------------------------
    Meagan McOlin
    ------------------------------



  • 4.  RE: Color-Coding Cells based on Dates AND Multi-Choice

    Posted 08-23-2021 16:18

    Austin,

    Basically, I'd like to know how to update the below code - it is written for a checkbox field type, but need it to work for a multi-choice field type instead (where the multi-choice options are Missed & Scheduled)

    If([Scheduled - Yes]=true,$green,

    If([Scheduled - Yes]=false and [Appointment Needed By]  >= Today(),$yellow,

    If([Scheduled - Yes]=false and [Appointment Needed By] < Today(),$red)))

    Thanks!



    ------------------------------
    Meagan McOlin
    ------------------------------