Project Condition Traffic light not Working

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I am recycling a tasks table I picked up from a template.  For some reason the project condition formula (in projects table) is not working.  There is some mismatch of the field types.  The formula is as under: 

Case([Status],"Parked", "https://images.quickbase.com/si/16/226-rect_gray.png\" title=\"Parked\">", "Active", 


If(ToDate([Start])>[Finish Date],"https://images.quickbase.com/si/16/227-rect_red.png\" title=\"Project Overdue\">",

If(ToDays([Total Days Overdue])>0,"https://images.quickbase.com/si/16/229-rect_yellow.png\" title=\"Task Overdue\">",

"https://images.quickbase.com/si/16/228-rect_green.png\" title=\"On Time\">")), 

"Completed", "https://images.quickbase.com/si/16/226-rect_gray.png\" title=\"Completed\">", 

"On Hold", "https://images.quickbase.com/si/16/226-rect_gray.png\" title=\"On Hold\">", 

"Cancelled", "https://images.quickbase.com/si/16/226-rect_gray.png\" title=\"Cancelled\">")

Start field is a work date field whereas Finish date is also work date field.  I have projected Finish date which was not working so I created Finish Date to lift info from Project Finish to get around the issue. Project Finish date  s also a work date. 

I have created a summary field in my projects to lift  max of start date and finish date. 

I am happy to continue using Project Finish Date. 

Will appreciate some help. 
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Try this:



Case([Status],
"Parked", "<img src=\"https://images.quickbase.com/si/16/226-rect_gray.png\"; title=\"Parked\">",
"Active",
If([Start]>[Finish Date], "<img src=\"https://images.quickbase.com/si/16/227-rect_red.png\"; title=\"Project Overdue\">",
If(ToDays([Total Days Overdue])>0, "<img src=\"https://images.quickbase.com/si/16/229-rect_yellow.png\"; title=\"Task Overdue\">",
"<img src=\"https://images.quickbase.com/si/16/228-rect_green.png\"; title=\"On Time\">")),

"Completed", "<img src=\"https://images.quickbase.com/si/16/226-rect_gray.png\"; title=\"Completed\">",
"On Hold", "<img src=\"https://images.quickbase.com/si/16/226-rect_gray.png\"; title=\"On Hold\">",
"Cancelled", "<img src=\"https://images.quickbase.com/si/16/226-rect_gray.png\"; title=\"Cancelled\">"
)




if that doesn't work, try this:



Case([Status],
"Parked", "<img src=\"https://images.quickbase.com/si/16/226-rect_gray.png\"; title=\"Parked\">",
"Active",
If(todate([Start])>[Finish Date], "<img src=\"https://images.quickbase.com/si/16/227-rect_red.png\"; title=\"Project Overdue\">",
If(ToDays([Total Days Overdue])>0, "<img src=\"https://images.quickbase.com/si/16/229-rect_yellow.png\"; title=\"Task Overdue\">",
"<img src=\"https://images.quickbase.com/si/16/228-rect_green.png\"; title=\"On Time\">")),

"Completed", "<img src=\"https://images.quickbase.com/si/16/226-rect_gray.png\"; title=\"Completed\">",
"On Hold", "<img src=\"https://images.quickbase.com/si/16/226-rect_gray.png\"; title=\"On Hold\">",
"Cancelled", "<img src=\"https://images.quickbase.com/si/16/226-rect_gray.png\"; title=\"Cancelled\">"
)
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks a lot Eric.  The econd solution is more likely to work but is stick Todays function dealing with Total Days Overdue saying that requirement of this function are not being met.
what type of field is Total Days Overdue in your app? should be a summary of Days Overdue on the tasks table, yes? what type of field is Days Overdue on the tasks table? what is the formula?
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks. It is a numeric summary field coming from a child task table.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
But otherwise it is duration field in the task table.
I show it would typically be a Duration (summary) field, not numeric. if it is numeric, just remove the todays part and just leave it: If([Total Days Overdue]>0,...
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks a lot. It works fine now.  Will need to look at the logic as currently i have all green.
ok. glad to hear it. you're welcome. to give you a head start, the reason why you have all green is because all your projects have a status of active, yet do not meet the project overdue and task overdue criteria. I thought that: if(todate([Start])>[Finish Date] seemed a little odd. I might go with something more like: if([estimated start]>[start] or [estimated finish]>[finish],//yellow//,

Good Luck! =]
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Many thanks.  I am actually happy to base this on the task completion than the project itself as various task completion feeds into project completion.  So I amended it to reflect all task fields.   Just from learning point of view,  these web addresses with different colour refs are really a given?  Are they?  If so, then I have a legend text field next to the PC field which also has some web addressing there.  No too sure if I need to amend the text field to be consistent with the web addresses.  I have following code sitting in this field:

<b>Legend</b><br>
<img src="https://images.quickbase.com/si/16/227-rect_red.png" title="Task Overdue"/> Task Overdue&nbsp;&nbsp;&nbsp;
     
    <img src="https://images.quickbase.com/si/16/229-rect_yellow.png" title="Task Over 7 Days Late"/> Task Over 7 Days Late&nbsp;&nbsp;&nbsp;
     
    <img src="https://images.quickbase.com/si/16/228-rect_green.png" title="On Time"/> On Time&nbsp;&nbsp;&nbsp;

<img src="https://images.quickbase.com/si/16/226-rect_gray.png" title="Inactive"/> Inactive
Photo of Tracy

Tracy

  • 0 Points
Would it possible for someone from QuickBase or Kirk Trachy to go into the actual app that's available for download and fix the issue with the Project Condition (ABC Project Manager 3)?