Forum Discussion

RachelMiller's avatar
RachelMiller
Qrew Cadet
2 years ago

Color coding formula based on another formula field

I have a formula rich text field called Last Opened Date with the following formula to trim a logged field so it only pulls out the date:

Trim(Right([*Vista Last Opened Date (Log)],10))

In my report, I want to color code a field called Report Name if the date in the Last Opened Date field is more than 120 days before today.  Can you please tell me what formula to use for this?  I've tried several and nothing has worked.

Thank You,

Rachel Miller



------------------------------
Rachel Miller
------------------------------
  • You'll need to make sure your report name is formula rich-text, and then something like this will work: 

    var bool overdue = (Today() - [Last Opened Date]) > Days(120);

    var text name = [Field that actually has the report name]; //This is the text that will show in your element

    "<p style='text-align:center; width: 100px; " & if( $overdue, "background: red; color:white;"'>" & $name & "</p>"

    The <p> tag just make it a block element so you can set a standard width to make the column look the same width up and down, adjust as needed. In the above I just have it setting the background to red and font-color to white, you can plug in your own hex color or alternate color though as needed. 



    ------------------------------
    Chayce Duncan
    ------------------------------
    • RachelMiller's avatar
      RachelMiller
      Qrew Cadet

      Thank you Chayce.  Do you know if I can change my Report Name field from text to a formula rich-text field without losing the data?



      ------------------------------
      Rachel Miller
      ------------------------------
      • ChayceDuncan's avatar
        ChayceDuncan
        Qrew Captain

        If [Report Name] is a text entry field, and not already formulaic then no, you do not want to change it given that all of the data will be lost. 

        If it is an entry field - then you would want a 'new' field that is formula rich text, and you will just display the value of [Report Name] instead and you would swap out to using the rich-text field on reports potentially so it showed with the style you're looking for. 



        ------------------------------
        Chayce Duncan
        ------------------------------
    • ChrisNewsome's avatar
      ChrisNewsome
      Qrew Captain

      Chayce, 

      I'm trying to do something similar but your last line above is missing a closing parentheses or something. I'm getting an error "expecting )," but i can't seem to figure out how to solve it. I've added the ), but the error just moves down to the </P> box with "expecting text." I've copied your formula and input my own fields, etc, otherwise I've made no changes. Any ideas?

      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend

        Chris,

        It's hard for us to help you without seeing your formula. Please copy and paste the code as an update to this thread.  ie not a screen shot but a copy paste of the code.