Forum Discussion

BuildPro's avatar
BuildPro
Qrew Captain
3 years ago

Formula assistance

This works...
If(ToText([Job - Related Sub Payroll22 - 2nd COAT (DAY 1) DATE])<ToText(Today()),
"<img src=\"https://images.quickbase.com/si/24/221-point_red.png\" title=\"DONE\">",
"<img src=\"https://images.quickbase.com/si/24/222-point_green.png\" title=\"TO DO\">")

AND, I need to add:
If(ToText([Job - Related Sub Payroll22 - 2nd COAT (DAY 1) DATE]) is blank,
"<img src=\"https://images.quickbase.com/si/24/223-point_yellow.png\" title=\"ALERT\">"

------------------------------
BuildPro
------------------------------
  • MikeTamoush's avatar
    MikeTamoush
    Qrew Commander
    3 years ago
    In general, to test if a text field is blank, you can just say [Text Field] ="", and to test if a date field is blank, you can do: is null([Date Field]).

    In your example, you would use the first way, but I am not sure why you are converting your dates to text? The following should work:

    var text PayrollField = [Job - Related Sub Payroll22 - 2nd COAT (DAY 1) DATE];

    If(
      $PayrollField < Today(), "<img src=\"https://images.quickbase.com/si/24/221-point_red.png\" title=\"DONE\">",
     isnull($PayrollField), "<img src=\"https://images.quickbase.com/si/24/223-point_yellow.png\" title=\"ALERT\">",
    "<img src=\"https://images.quickbase.com/si/24/222-point_green.png\" title=\"TO DO\">"
     )

    ------------------------------
    Mike Tamoush
    ------------------------------
    • BuildPro's avatar
      BuildPro
      Qrew Captain
      3 years ago
      Worked perfectly.

      I thought I needed to convert date field to TEXT because field type is RICH TEXT FORMULA?  Evidently not.  Thx

      ------------------------------
      BuildPro
      ------------------------------
      • MikeTamoush's avatar
        MikeTamoush
        Qrew Commander
        3 years ago
        Actually I am surprised it worked, it should be

        var date PayrollField = .... (instead of var text)

        The type of field only determines what the final OUTPUT of the formula is. Meaning, in this case the outcome is a picture/text of 'Alert', 'Done', etc. So your outcome is rich text, so that works.

        You can test and manipulate anything you want (dates, numbers, etc) as long as the final OUTPUT is text

        ------------------------------
        Mike Tamoush
        ------------------------------