Text Mode Report: IF Statement in column, to catch us not meeting promised dates | Community
Skip to main content
C
CatherineKe
New Participant
November 3, 2022
Solved

Text Mode Report: IF Statement in column, to catch us not meeting promised dates

  • November 3, 2022
  • 1 reply
  • 932 views

I am working with a task report, and attempting to add a column using text mode to show if the task is on time or late, based on "Date Needed." This s a date entered into a field on the project custom form.

 

I’ve struggled to get it to work, so I backed off to get a more straight forward valueexpression to work.

 

This “Task Late?” column works

displayname=Task Late?

linkedname=direct

namekey=plannedStartDate

querysort=plannedStartDate

textmode=true

valueexpression=IF({plannedCompletionDate}<{projectedCompletionDate},"Late","On Time")

valueformat=HTML

 

Note: The caveat to the above is that it seems to show Late or On Time correctly for the most part, but there are a few that show “late” even though the dates are the same. I think it might be considering the time. How can I tell it to ignore the time, and only consider the date?

 

What I really want to use though, is the Date Needed field from a custom form, compared to the task planned completion date.

This is what I’ve tried, but every item is displaying On Time.

displayname=Proj Late?

linkedname=project

namekey=view.relatedcolumn

namekeyargkey.0=project

namekeyargkey.1=Date Needed

querysort=DE:project:Date Needed

textmode=true

valueexpression=IF({DE:project:Date Needed}<{plannedCompletionDate},"Late","On Time")

valueformat=HTML

 

Ideally, I’d like to Filter the entire report to only show the projects where the Date Needed (project) is less than the task planned completion date – so it would show me the projects that are currently running behind the date it is needed. I don’t think that’s possible, though?

 

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by CatherineKe

I am replying to my own post, with an update in case it may help anyone else. And, I have one outstanding question.

I figured out the valuexpression I needed in order to show if the project is Late or On Time using the field from a custom form:

 

valueexpression=IF({project}.{DE:project:Date Needed}<{plannedCompletionDate},"LATE","On Time")

 

I also realized I can accomplish this in another way by comparing the fields and using conditional formatting to highlight the late projects. I like this approach better. However, either way I still have the issue of some projects showing as Late, even though the Date Needed is the same as the task Planned Completion Date.

 

I think I have to utilize CLEARTIME, but can't figure out the syntax to use it. Can anyone help with that, please?

  • The valueexpression used in the "Proj Late?" field is above for reference
  • Or, the text mode with the conditional formatting to compare the fields is below
displayname=Project Due Date linkedname=project namekey=view.relatedcolumn namekeyargkey.0=project namekeyargkey.1=Date Needed querysort=DE:project:Date Needed row.0.styledef.applyallcases=true row.0.styledef.case.0.comparison.icon=false row.0.styledef.case.0.comparison.isrowcase=true row.0.styledef.case.0.comparison.leftmethod=DE:project:Date Needed row.0.styledef.case.0.comparison.lefttext=DE:project:Date Needed row.0.styledef.case.0.comparison.operator=lt row.0.styledef.case.0.comparison.operatortype=date row.0.styledef.case.0.comparison.rightmethod=plannedCompletionDate row.0.styledef.case.0.comparison.righttext=plannedCompletionDate row.0.styledef.case.0.comparison.trueproperty.0.name=bgcolor row.0.styledef.case.0.comparison.trueproperty.0.value=eac6c9 row.0.styledef.case.0.comparison.truetext= sortOrder=1 sortType=asc styledef.case.0.comparison.icon=false styledef.case.0.comparison.isrowcase=true styledef.case.0.comparison.leftmethod=DE:project:Date Needed styledef.case.0.comparison.lefttext=DE:project:Date Needed styledef.case.0.comparison.operator=lt styledef.case.0.comparison.operatortype=date styledef.case.0.comparison.rightmethod=plannedCompletionDate styledef.case.0.comparison.righttext=plannedCompletionDate styledef.case.0.comparison.trueproperty.0.name=bgcolor styledef.case.0.comparison.trueproperty.0.value=eac6c9 styledef.case.0.comparison.truetext= textmode=true valuefield=project:Date Needed valueformat=customDateAsString

Thank you!

 

1 reply

C
CatherineKeAuthorAccepted solution
New Participant
January 19, 2023

I am replying to my own post, with an update in case it may help anyone else. And, I have one outstanding question.

I figured out the valuexpression I needed in order to show if the project is Late or On Time using the field from a custom form:

 

valueexpression=IF({project}.{DE:project:Date Needed}<{plannedCompletionDate},"LATE","On Time")

 

I also realized I can accomplish this in another way by comparing the fields and using conditional formatting to highlight the late projects. I like this approach better. However, either way I still have the issue of some projects showing as Late, even though the Date Needed is the same as the task Planned Completion Date.

 

I think I have to utilize CLEARTIME, but can't figure out the syntax to use it. Can anyone help with that, please?

  • The valueexpression used in the "Proj Late?" field is above for reference
  • Or, the text mode with the conditional formatting to compare the fields is below
displayname=Project Due Date linkedname=project namekey=view.relatedcolumn namekeyargkey.0=project namekeyargkey.1=Date Needed querysort=DE:project:Date Needed row.0.styledef.applyallcases=true row.0.styledef.case.0.comparison.icon=false row.0.styledef.case.0.comparison.isrowcase=true row.0.styledef.case.0.comparison.leftmethod=DE:project:Date Needed row.0.styledef.case.0.comparison.lefttext=DE:project:Date Needed row.0.styledef.case.0.comparison.operator=lt row.0.styledef.case.0.comparison.operatortype=date row.0.styledef.case.0.comparison.rightmethod=plannedCompletionDate row.0.styledef.case.0.comparison.righttext=plannedCompletionDate row.0.styledef.case.0.comparison.trueproperty.0.name=bgcolor row.0.styledef.case.0.comparison.trueproperty.0.value=eac6c9 row.0.styledef.case.0.comparison.truetext= sortOrder=1 sortType=asc styledef.case.0.comparison.icon=false styledef.case.0.comparison.isrowcase=true styledef.case.0.comparison.leftmethod=DE:project:Date Needed styledef.case.0.comparison.lefttext=DE:project:Date Needed styledef.case.0.comparison.operator=lt styledef.case.0.comparison.operatortype=date styledef.case.0.comparison.rightmethod=plannedCompletionDate styledef.case.0.comparison.righttext=plannedCompletionDate styledef.case.0.comparison.trueproperty.0.name=bgcolor styledef.case.0.comparison.trueproperty.0.value=eac6c9 styledef.case.0.comparison.truetext= textmode=true valuefield=project:Date Needed valueformat=customDateAsString

Thank you!

 

Terms & ConditionsAccessibility statement

Loading footer...