Discussions

 View Only
Expand all | Collapse all

Task Condition html for Date Completed

Yehudah Greenberg

Yehudah Greenberg08-03-2017 23:16

  • 1.  Task Condition html for Date Completed

    Posted 08-03-2017 19:37
    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?


  • 2.  RE: Task Condition html for Date Completed

    Posted 08-03-2017 21:59
    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)


  • 3.  RE: Task Condition html for Date Completed

    Posted 08-03-2017 22:55
    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.


  • 4.  RE: Task Condition html for Date Completed

    Posted 08-03-2017 23:08
    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.


  • 5.  RE: Task Condition html for Date Completed

    Posted 08-03-2017 23:15
    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.


  • 6.  RE: Task Condition html for Date Completed

    Posted 08-03-2017 23:16
    I need the work date for predecessors. 


  • 7.  RE: Task Condition html for Date Completed

    Posted 08-03-2017 23:30
    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])


  • 8.  RE: Task Condition html for Date Completed

    Posted 08-03-2017 23:38
    start" is a formula work set to "date created", and that's what come up.  (I forget why I set it like that...)


  • 9.  RE: Task Condition html for Date Completed

    Posted 08-03-2017 23:39
    Oh, that's right.  I set the "start" to "date created" to avoid the problem of the undated field that has infinite values.


  • 10.  RE: Task Condition html for Date Completed

    Posted 08-03-2017 23:40
    One solution I thought of is to set the default end date to Jan 2999.  But that seems a little sloppy to me.


  • 11.  RE: Task Condition html for Date Completed

    Posted 08-03-2017 23:57
    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.


  • 12.  RE: Task Condition html for Date Completed

    Posted 08-04-2017 00:01
    You may be right...  I'll sit down when I have some time and start from scratch.  I'll let you know!


  • 13.  RE: Task Condition html for Date Completed

    Posted 08-04-2017 02:14
    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?


  • 14.  RE: Task Condition html for Date Completed

    Posted 08-04-2017 02:50
    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.


  • 15.  RE: Task Condition html for Date Completed

    Posted 08-04-2017 03:56
    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\">"

    )))))


  • 16.  RE: Task Condition html for Date Completed

    Posted 08-04-2017 12:02
    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.


  • 17.  RE: Task Condition html for Date Completed

    Posted 08-08-2017 01:35
    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.
     


  • 18.  RE: Task Condition html for Date Completed

    Posted 08-08-2017 09:41
    Please post your whole formula. That piece ends in a comma, so I assume it's just a partial formula.


  • 19.  RE: Task Condition html for Date Completed

    Posted 08-08-2017 13:10
    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\">"

    )))))))))))


  • 20.  RE: Task Condition html for Date Completed

    Posted 08-08-2017 13:10
    CORRECTION:  A work date is not a text field, but the "predecessor" is.  


  • 21.  RE: Task Condition html for Date Completed

    Posted 08-08-2017 19:27
    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".


  • 22.  RE: Task Condition html for Date Completed

    Posted 08-08-2017 19:49
    Ok, thx for letting me know that you are good. In future, please post the complete syntax error message so we have complete information.