Forum Discussion

YehudahGreenber's avatar
YehudahGreenber
Qrew Assistant Captain
8 years ago

Task Condition html for Date Completed

In Project Manager, I'm using HTML to link the Task Condition buttons to Start and End Dates, as opposed to the manually updated "status" menu.  This way, the "inactive" button will appear automatically when a task is marked complete.  Trouble when I try use the "actual end date" as a trigger, because apparently, while it's undefined it has infinite value, and therefore every argument is true.

 Here's the code: If
(Today()>=ToDate([Date Complete]) and ToDate([Date Complete]) >= (ToDate([Projected Finish])), "<img src=\"https://images.quickbase.com/si/16/220-point_gray.png\"; title=\"Inactive\">"

No matter how many "and's" I add, "Date Complete" is dominant, and I get the grey button, apparently because until the user manually enters the date, it is undefined and therefore has all values.  Is there a way around this?
Ask about Quickbase functionality
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    I'm not really understanding the question, but you can do this, for example

    IF(
    IsNull([some date field]),"", 

    Today()>=ToDate([Date Complete]) and ToDate([Date Complete]) >= (ToDate([Projected Finish])), "<img src=\"https://images.quickbase.com/si/16/220-point_gray.png\"; title=\"Inactive\">")


    Quick Base processes the first test first and it is is true, it does not evaluate any further.

    So you just line up your tests sequentially

    IF(
    this is true, result 1,
    that is true, result 2,
    something else is true, result 3)
  • YehudahGreenber's avatar
    YehudahGreenber
    Qrew Assistant Captain
    8 years ago
    Sorry for not being clear, here is the whole script:

    If (ToDate([Start])>(Today()), "<img src=\"https://images.quickbase.com/si/16/049-clock.png\"; title=\"Delayed Start\">",

    If (Today()=ToDate([Start]) and (Today()<=ToDate([Projected Finish])), "<img src=\"https://images.quickbase.com/si/16/222-point_green.png\"; title=\"On Time\">",

    If (Today()>ToDate([Projected Finish]) and (Today()<ToDate([Date Complete])), "<img src=\"https://images.quickbase.com/si/16/221-point_red.png\"; title=\"Overdue\">",

    If ([Status]="Issue", "<img src=\"https://images.quickbase.com/si/16/223-point_yellow.png\"; title=\"Issue\">",

    If (Today()>=ToDate([Date Complete]) and ToDate([Date Complete]) >= (ToDate([Projected Finish])), "<img src=\"https://images.quickbase.com/si/16/220-point_gray.png\"; title=\"Inactive\">",
    "<img src=\"https://images.quickbase.com/si/16/220-point_gray.png\"; title=\"Inactive\">",


    )))))


    Every task has  an "inactive" button, regardless of the other attributes.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    As a matter of simpler syntax, I would get rid if the nested IFs and hence be able to get rids of the )))))) at the end of the formula.  It would just with 1 ).  But that will not fix your problem, just make it easier in the future for you write QuickBase formulas.

    That is needless nesting because that is how you need to do it in excel, but you do not have to do it that way in Quick Base.

    Can you tell me what field type the field [Start] is?  it would be unusual to have to convert a field to a Date field unless it was a date/time field type. Maybe Start is just a text field?

    For example if you make a stand alone field with the formula ToDate([start]) what is the result.
  • YehudahGreenber's avatar
    YehudahGreenber
    Qrew Assistant Captain
    8 years ago
    Yeah, sloppy coding...  I really don't know anything about code, that's why I'm using quickbase!  The nesting was part of the default conditions in the Project Manager, and I'm trying to adapt that.  The Start date is a work date and I think it needed conversion.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    so on an example record, what is in the field for Start Date and what is the result of a test formula of ToDate([Start Date])
  • YehudahGreenber's avatar
    YehudahGreenber
    Qrew Assistant Captain
    8 years ago
    start" is a formula work set to "date created", and that's what come up.  (I forget why I set it like that...)
  • YehudahGreenber's avatar
    YehudahGreenber
    Qrew Assistant Captain
    8 years ago
    Oh, that's right.  I set the "start" to "date created" to avoid the problem of the undated field that has infinite values.
  • YehudahGreenber's avatar
    YehudahGreenber
    Qrew Assistant Captain
    8 years ago
    One solution I thought of is to set the default end date to Jan 2999.  But that seems a little sloppy to me.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    You have code but you may not understand the logic that you want.

    I think that you need to be able to express in words what the formula should do and then write the formula to do what you want.

    You may just be starting with a templated app and the test data you have simply is not meeting the IF conditions.