Skip to main content
Question

Task Condition html for Date Completed

  • August 3, 2017
  • 0 replies
  • 75 views
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?
    This topic has been closed for replies.

    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)
    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.
    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.
    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.
    I need the work date for predecessors. 
    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])
    start" is a formula work set to "date created", and that's what come up.  (I forget why I set it like that...)
    Oh, that's right.  I set the "start" to "date created" to avoid the problem of the undated field that has infinite values.
    One solution I thought of is to set the default end date to Jan 2999.  But that seems a little sloppy to me.
    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.
    You may be right...  I'll sit down when I have some time and start from scratch.  I'll let you know!
    Okay, I've worked it out, and here is the problem.  I want the grey button to appear when only one change is made to the form-  that is, when the end date is inserted.  Until the html can detect that the end date has gone from potentially anything to a real value, there is no way I can get the button to appear when I want it to.  
    I've thought of a workaround, in which I created a "done button"  (a radio button)  and I would link the end date to the radio button as follows:  When marked "done", then date complete = Today.  I can's seem to code this though...  Does the formula date field accept html?
    I got it now.  I didn't know that "isnull" identifies an undefined value.  Now I can do it.   Will post entire code after I get it to work.
    Works great thank you.  Here it is.

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

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

    If (IsNull(ToDate([Date Complete])) and (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 (IsNull(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\">"

    )))))
    Great, glad it's working.

    Just a tip. The IsNull will NOT work to check if a text field is blank.

    To check for that use those

    If(Trim([My text field])="", "missing text field")

    The IsNull is documented to not work on text fields and that is not intuitive and will make you crazy trying to debug a formula if you don't understand that.
    Hi, I'm back.  Thanks for the if(trim) tip, as it turns out that workdates are considered text.  

    Why can't I get the following to work:  If ([Duration]>10),  "<img src=\"https://images.quickbase.com/si/16/223-point_yellow.png\"; title=\"Issue\">",       ?

    "Duration" is a numeric field.  I get an error that I am not following Boolean logic.
     
    Please post your whole formula. That piece ends in a comma, so I assume it's just a partial formula.
    If ((Trim([Predecessors])="") and (Today()>=ToDate([Date Complete])), "<img src=\"https://images.quickbase.com/si/16/220-point_gray.png\"; title=\"Inactive\">",

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

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

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

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

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

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

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

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

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

    If (IsNull(ToDate([Date Complete])) and (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 (IsNull(ToDate([Date Complete]))), "<img src=\"https://images.quickbase.com/si/16/221-point_red.png\"; title=\"Overdue\">"

    )))))))))))
    CORRECTION:  A work date is not a text field, but the "predecessor" is.  
    Got it.  It should be : If ([Duration]>10,  "<img src=\"https://images.quickbase.com/si/16/223-point_yellow.png\"; title=\"Issue\">",       without closing the parenthesis after "10".
    Ok, thx for letting me know that you are good. In future, please post the complete syntax error message so we have complete information.
    Terms & ConditionsAccessibility statement