Forum Discussion

MeaganMcOlin's avatar
MeaganMcOlin
Qrew Trainee
3 years ago

Color-Coding Cells based on Dates AND Multi-Choice

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
------------------------------

Formulas and functions

3 Replies

Sort By
  • AustinK's avatar
    AustinK
    Qrew Commander
    3 years ago
    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.
    • MeaganMcOlin's avatar
      MeaganMcOlin
      Qrew Trainee
      3 years ago

      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
      ------------------------------
  • MeaganMcOlin's avatar
    MeaganMcOlin
    Qrew Trainee
    3 years ago

    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
    ------------------------------