Task Condition html for Date Completed

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
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?
Photo of Yehudah Greenberg

Yehudah Greenberg

  • 600 Points 500 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
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)
Photo of Yehudah Greenberg

Yehudah Greenberg

  • 600 Points 500 badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
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.
Photo of Yehudah Greenberg

Yehudah Greenberg

  • 600 Points 500 badge 2x thumb
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.
Photo of Yehudah Greenberg

Yehudah Greenberg

  • 600 Points 500 badge 2x thumb
I need the work date for predecessors. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
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])
Photo of Yehudah Greenberg

Yehudah Greenberg

  • 600 Points 500 badge 2x thumb
"start" is a formula work set to "date created", and that's what come up.  (I forget why I set it like that...)
Photo of Yehudah Greenberg

Yehudah Greenberg

  • 600 Points 500 badge 2x thumb
Oh, that's right.  I set the "start" to "date created" to avoid the problem of the undated field that has infinite values.
Photo of Yehudah Greenberg

Yehudah Greenberg

  • 600 Points 500 badge 2x thumb
One solution I thought of is to set the default end date to Jan 2999.  But that seems a little sloppy to me.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
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.
Photo of Yehudah Greenberg

Yehudah Greenberg

  • 600 Points 500 badge 2x thumb
You may be right...  I'll sit down when I have some time and start from scratch.  I'll let you know!
Photo of Yehudah Greenberg

Yehudah Greenberg

  • 600 Points 500 badge 2x thumb
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?
Photo of Yehudah Greenberg

Yehudah Greenberg

  • 600 Points 500 badge 2x thumb
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.
Photo of Yehudah Greenberg

Yehudah Greenberg

  • 600 Points 500 badge 2x thumb
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\">"

)))))
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
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.
Photo of Yehudah Greenberg

Yehudah Greenberg

  • 600 Points 500 badge 2x thumb
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.
 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
Please post your whole formula. That piece ends in a comma, so I assume it's just a partial formula.
Photo of Yehudah Greenberg

Yehudah Greenberg

  • 600 Points 500 badge 2x thumb
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\">"

)))))))))))
Photo of Yehudah Greenberg

Yehudah Greenberg

  • 600 Points 500 badge 2x thumb
CORRECTION:  A work date is not a text field, but the "predecessor" is.  
Photo of Yehudah Greenberg

Yehudah Greenberg

  • 600 Points 500 badge 2x thumb
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".
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
Ok, thx for letting me know that you are good. In future, please post the complete syntax error message so we have complete information.