Status field for a duration not working properly

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • In Progress
I have a duration field called [Contract Down Time]. I would like to put a status on this that would return "Downtime Reported" if there is a value in the field, and "No Downtime" if the field was left blank. I thought I knew how to do this, but it's not working. I have the following formula in the status field:

If(ToText([Contract Down Time])<>"",

"<div class='" & "<b>" & "Downtime" & "</b>" & "' style='padding: 3px 5px; background: #" & "FF1493" & " ; border-radius: 8px; text-align: center; text-decoration: none'>" & "<b>" & "Downtime Reported" & "</b>" & "</div>",

"<div class='" & "<b>" & "No Downtime" & "</b>" & "' style='padding: 3px 5px; background: #" & "E0E0E0" & "; border-radius: 8px; text-align: center; text-decoration: none'>" & "<b>" & "No Downtime" & "</b>" & "</div>"

)


However when I look at one record with downtime and another without, they both have the status "Downtime Reported"

Can someone help me figure out what I have done wrong on this?

Thanks!
Photo of Andreonna

Andreonna

  • 1,040 Points 1k badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
Try to use the IsNull function rather than the <>"".

If(IsNull([Contract Down Time]),

If the field properties for the duration field have "treat blank values as '0' in calculations" it will cause your <>"" to have the value of 0, so its not ever blank.
Photo of Andreonna

Andreonna

  • 1,040 Points 1k badge 2x thumb
This helped partially. I had to uncheck the "treat blanks as 0" on my duration field. Unchecking that seems to have worked. 

The problem now is that if it's blank the status works, but if my employees filling the report out put a zero in there (which they do all the time) it doesn't work. 

Any suggestions?
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
If([Contract Down Time]<=0,   ....

Or do they put 0 and you want that to be true if they put 0?
Photo of Andreonna

Andreonna

  • 1,040 Points 1k badge 2x thumb
This is what I originally tried. I couldn't do that because the status field is text and the [Contract Down Time] field is a duration. 
Photo of Michael Barrow

Michael Barrow

  • 2,206 Points 2k badge 2x thumb
If you want to catch both a null and a blank, for any field type, my experience is that it's best to use the Length function (in conjunction with ToText for non-text fields).

If( Length(ToText([Non-textField])) = 0, <it's null or blank>, <it's not null or blank> )

It really irritates me that QB decided to make the IsNull function not work right with text fields. That makes no sense to me, but the Length work-around does the trick.
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
To confirm, If they 'report' 0 days or 1000 days, you want the status to be "Downtime Reported"?

But if its blank you want it to be "no downtime"?

Or if its Zero is should be "no downtime"?
Photo of Andreonna

Andreonna

  • 1,040 Points 1k badge 2x thumb
If blank or zero there should be no downtime.