Discussions

Expand all | Collapse all

Using Case function to determine the color of a field

  • 1.  Using Case function to determine the color of a field

    Posted 06-27-2017 15:58
    I can't seem to get this formula working, I want if the the [Days Till Due] is greater then zero there is no change. If it is -1 or -2 then it is yellow and if it is -3 then it is red

    Case([Days Till Due], [Days Till Due] <= -3, "<div style=\"color:black; background-color:Red;\">&[Days Till Due]&</div>",   [Days Till Due] >= -2 and [Days Till Due] < 0,"<div style=\"background-color:Yellow;\">&[Days Till Due]&</div>", "")


  • 2.  RE: Using Case function to determine the color of a field

    Posted 06-27-2017 18:03
    What type of field is "Days Till Due"?


  • 3.  RE: Using Case function to determine the color of a field

    Posted 06-27-2017 18:05
    It is a numeric calculation 


  • 4.  RE: Using Case function to determine the color of a field

    Posted 06-28-2017 05:51
    Just a little syntax error.  
    Case([Field],
    evaluation, answer is true,
    evaluation, answer if true, 
    answer if non-else)

    Try this:

    Case([Days Till Due],
    -3, "<div style=\"color:black; background-color:Red;\">&[Days Till Due]&</div>",
    -2, "<div style=\"background-color:Yellow;\">&[Days Till Due]&</div>",
    -1, "<div style=\"background-color:Yellow;\">&[Days Till Due]&</div>", "")


  • 5.  RE: Using Case function to determine the color of a field

    Posted 06-28-2017 12:42
    It partially worked in that the color did appear correctly when the due date was -1, I would like it so anything -3 or less would appear in red. So the -5 seen in the picture would appear in red, also with the numbers showing



  • 6.  RE: Using Case function to determine the color of a field

    Posted 06-28-2017 12:46
    Try this:
    if(  [Days Till Due] <= -3, "<div style=\"color:black; background-color:Red;\">"&[Days Till Due]&"</div>",   [Days Till Due] >= -2 and [Days Till Due] < 0,"<div style=\"background-color:Yellow;\">"&[Days Till Due]&"</div>",  "")


  • 7.  RE: Using Case function to determine the color of a field

    Posted 06-28-2017 12:48
    This worked! Thank you!