Forum Discussion

bottyz's avatar
bottyz
Qrew Member
16 days ago
Solved

Color Code Date Field based on Formula

Hi there,

New to Quickbase, but have a big MS Excel background. What I used to find easy in Excel, I can't seem to get my head around in QB. If anyone can help enlighten my rookie brain, it would be greatly appreciated.

I'm trying to colour-code a date type field based on multiple conditions:

Say for example I have field called 'Survey Required By', which is the date for when we want a survey to be completed, and then another field called 'Survey Completed', and in this field we enter the date when the survey is completed.

I want to highlight the field text or background in red if the 'Survey Required By' date is today or older, and the 'Survey Completed' field is blank.

I've googled and tired many different formulas, but the best I've managed is to color code a whole row on a report, which I don't want to do. I just want the 1 field color changed.

The formula I have so far is:

if(
isnull([Survey Completed]) and [survey required by]=today(),
"red",""
)

 

 

 

 

formula query
  • MarkShnier__You's avatar
    MarkShnier__You
    11 days ago

    What is the name of your Rich Text field?  Try adding it to the report.  :)

11 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    15 days ago

    You will need to create a new Formula Rich Text field.  It's not like Excel where you can format the cell  

    here are some examples:


    "<div style=\"color:black;background-color:red;\"><div>" & [Answer]

     

    or for a simple bold red 

    "<font color=red><b>" & [my text field] 

    • bottyz's avatar
      bottyz
      Qrew Member
      14 days ago

      hi mark,

      Thanks for the reply.

      If I was to change it to a formula rich text field, would that mean it won't naturally format into a date? i.e. appear on the record as 04-JUN-2024?
      And will the user see the formatting code? And therefore be able to accidentally change it?

       

       

      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        14 days ago

        Perhaps I didn't communicate clearly enough. Your date field will remain a date field. In addition to the date field, you will set up a brand new formula Rich Text  field to present that date field on various forms or report as a Rich Text field. 

        like this

        "<font color=red><b>" & ToText([my date field])

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    13 days ago

    In order to check whether a text field is blank you need to check it against empty quotes. It is obscure, but it is documented that The is no function. Does not work on text fields.

     

    In order to check whether a text field is blank you need to check it against empty quotes. It is obscure, but it is documented that The is no function does not work on text fields.

     

    So if [survey completed] as a text, feel like yes no,

     

    then use

     

    if([Survey completed] = ""

    another tip is that if you were checking a freeform data entry text field to be blank. I do this.

     

    if (Trim([my field]) = "", ......

    Just in case the user has entered some empty spaces in the field

     

     

     

    • bottyz's avatar
      bottyz
      Qrew Member
      13 days ago

      Hi Mark,

      Ok I've tried that but get another error as the [Survey Completed] field is a date format field, ="" doesn't then work.


       

      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        13 days ago

        Ok, so both fields are date fields. Then IsNull should work fine.

         

        i would think this should work.

         

        If( IsNull([Survey Completed]) and [Survey Required By]<=Today(), "<div style=\"color:white;background-color:red;\">" & ToText([Survey Required By]) & "<div>" , 

        ToText([Survey Required By])

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    11 days ago

    What is the name of your Rich Text field?  Try adding it to the report.  :)

    • bottyz's avatar
      bottyz
      Qrew Member
      11 days ago

      Ah ha! I understand now... this new field replaces the original in the report!

      Thanks for pointing that out!

      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        11 days ago

        It's hard to unlearn Excel.  I used to be pretty good at Excel, but now if I have to do a complicated formula in Excel I have to unlearn Quickbase. 

        This video says it all