Expand my Community achievements bar.

Latest Community Ideas Review is Out: Discover What’s New and What to Expect!

Help: Text Mode for Conditional Formatting on Current Task Details in a Project Report

Avatar

Level 3

I have a Project Report that pulls in the current task details (assignee name, task, and planned completion date) using the text mode below:

 

displayname=Current Task Details
listdelimiter=<p>
listmethod=nested(tasks).lists
textmode=true
type=iterate
valueexpression=IF({canStart}=true && {status}!='CPL' && {numberOfChildren}=0,CONCAT("•",{assignedTo}.{name},": ",{name},"- ",{plannedCompletionDate}))
valueformat=HTML
width=270

I would like the cell to highlight red if the current task due date has passed. I can get the text mode to work if the planned completion date of the project has passed using the code below, but not sure how to write the code to work for the current task's planned completion date. Can anyone help? 

styledef.case.0.comparison.icon=false
styledef.case.0.comparison.leftmethod=plannedCompletionDate
styledef.case.0.comparison.lefttext=plannedCompletionDate
styledef.case.0.comparison.operator=lte
styledef.case.0.comparison.operatortype=date
styledef.case.0.comparison.righttext=$$TODAY
styledef.case.0.comparison.trueproperty.0.name=bgcolor
styledef.case.0.comparison.trueproperty.0.value=eac6c9
styledef.case.0.comparison.truetext=

16 Replies

Avatar

Community Advisor

Hi Sarah, 

The text mode used to display task-level data in a project report is called a collection. Unfortunately, collections have limited controls and are incompatible with conditional formatting.

If the conditional formatting is critical to your report, then you might consider using a task report instead, and grouping by project. 

If you like my content, please take a moment to view and vote on my Idea Requests: https://tinyurl.com/4rbpr7hf

Avatar

Level 3

Bummer. About 80% of my columns are at the project level, this is the only column I pull in at the task level so I don't think a task report would work because I need some of my columns to be inline editiable, such as the Project Status. 

Avatar

Level 6

Howdy! 

 

For many years I too wanted to achieve the above yet all told me "not possible." Well guess what... with the right text mode  anything is possible. I picked the brain of one of my web developers and they helped me piece together the code to achieve the below. As soon as I figured it out I HAD to share with others. And this is the first thread that came up on the topic  ENJOY! Sorry for the multiple posts but I'm quickly hitting the character limit here haha.

 

What it does...

On a project report, pulls in the current task, ready to start but not complete. It pulls each item in with an emoji next to it, and if there are multiple items, each should go to its own line. This makes it easier to read when multiple tasks are happening on the project at a given time.

lindselib_0-1683552879444.png

 

Avatar

Level 6
  • The icon next to the task due date will appear green if today or greater, and red if past due. If the task is assigned to a person, a smile emoji and the person’s name will display, if assigned to a team a group of people and the team name will display, and if the task is unassigned a question mark and the words “unassigned” will display.

lindselib_6-1683553228158.png

 

lindselib_7-1683553228160.png

 

And yes this means that you can now search the report for  “unassigned” or for a specific person or team name and it will pull up those items sitting with them.

Avatar

Level 6

Here is the cheat code to achieve this awesomeness...

 

TASK NAME

displayname=Current Task
listdelimiter=<div>
listmethod=nested(tasks).lists
textmode=true
type=iterate
valueexpression=IF({numberOfChildren}=0,IF({canStart},IF(CONTAINS("CPL",{status}),"",CONCAT("✔ ",{name}))))
valueformat=HTML

 

TASK ASSIGNMENT

displayname=Task Assigned To
listdelimiter=<div>
listmethod=nested(tasks).lists
textmode=true
type=iterate
valueexpression=IF({numberOfChildren}=0,IF({canStart},IF(CONTAINS("CPL",{status}),"",IF({assignedToID}!="",CONCAT("REPLACE WITH EMOJI ",{assignedTo}.{name}),IF({team}.{ID}!="",CONCAT("REPLACE WITH EMOJI ",{team}.{name})," ? Unassigned")))))
valueformat=HTML

 

TASK DUE DATE

displayname=Task Due
listdelimiter=<div>
listmethod=nested(tasks).lists
textmode=true
type=iterate
valueexpression=IF({numberOfChildren}=0,IF({canStart},IF(CONTAINS("CPL",{status}),"",IF({plannedCompletionDate}<$$TODAY,CONCAT(" REPLACE WITH EMOJI ",{plannedCompletionDate}),CONCAT("🟢 ",{plannedCompletionDate})))))
valueformat=HTML

Avatar

Level 3

This is a great looking idea. Thank you for sharing but it could be not all of the text code was posted. I don't see the same results you shared in your screenshots. It doesn't have the different icons available, just two, the check mark and the green circle.

 

I may be missing something but is there additional text code that should be included? Perhaps it got cut off?

 

Thanks

Avatar

Level 6

Yes sorry about this and I've tried to update a couple times but the forum here doesn't seem to like the emoji's I used! I have instead put bold UPDATE WITH EMOJI in the spots that the emoji's are failing to stick. When you use this in your instance simply replace the bold text with the emoji shown in my initial post (or the emoji of your choice). I can do this by right clicking in my textmode editor and clicking "emoji." Hope that helps!

Avatar

Level 3

Thank you! 

---------------------------------------------

🚴

Avatar

Level 6

*Credit due too to Jason from Workfront consulting, for helping teach me some of the text mode to lay the foundation for the above. If you are on the fence about paying for consulting JUST DO IT! So worth it.

Avatar

Level 2

This is SO amazing. I was wondering if you could help me work out using the value text to pull in a specific milestone? Right now I'm pulling in the 'AUDITS RECEIVED' milestone into this column and noting if it's due or completed. If it's still due, how do I add this if statement to then put the red dot if the planned completion date is less than today? I'm trying but text mode formulas are still a mystery to me. This is my current value expression:

 

valueexpression=IF({milestone}.{name}="AUDITS RECEIVED",IF({status}="DED","N/A",IF({status}!="CPL",CONCAT("Due ",{plannedCompletionDate}),CONCAT("Completed ",{actualCompletionDate}))))

 

This is what I have so far: valueexpression=IF({milestone}.{name}="AUDITS RECEIVED",IF(AND({status}="DED", {plannedCompletionDate}<$$TODAY()), " N/A",IF(AND({status}="DED", {plannedCompletionDate}>$$TODAY()), "🟢 N/A",IF(CONTAINS("CPL",{status}), CONCAT("Completed ",{actualCompletionDate}),IF({plannedCompletionDate}<TODAY(), CONCAT(" ",{plannedCompletionDate}),CONCAT("🟢 ",{plannedCompletionDate}))))))

Avatar

Level 2

This value expression is successfully showing the milestones that are complete or that are not complete but not overdue: valueexpression=IF({milestone}.{name}="STRATEGY APPROVED",IF(AND({status}="DED", {plannedCompletionDate}<$$TODAY), " N/A",IF(AND({status}="DED", {plannedCompletionDate}>$$TODAY), "🟢 N/A",IF(CONTAINS("CPL",{status}), CONCAT("Completed ",{actualCompletionDate}),IF({plannedCompletionDate}<$$TODAY, CONCAT(" ",{plannedCompletionDate}),CONCAT("🟢 ",{plannedCompletionDate}))))))

 

Any ideas on where I'm going wrong for the overdue milestones?

Avatar

Level 6

Hmmm maybe try.....

 

valueexpression=IF({milestone}.{name}="AUDITS RECEIVED",IF(CONTAINS("DED",{status}),"N/A",IF({plannedCompletionDate}<$$TODAY,CONCAT("",{plannedCompletionDate}),CONCAT("🟢 ",{plannedCompletionDate}))))

 

Or if you wanted it to say something different when complete, try...

 

valueexpression=IF({milestone}.{name}="AUDITS RECEIVED",IF(CONTAINS("DED",{status}),"N/A",IF(CONTAINS("CPL",{status}),"Complete",IF({plannedCompletionDate}<$$TODAY,CONCAT("",{plannedCompletionDate}),CONCAT("🟢 ",{plannedCompletionDate})))))

 

Hope that helps! I think the crux of it is you need to reference the task status with that "contains" statement.

 

Thanks,

Lindsey

Avatar

Level 2

The second one is ALMOST there, but I can't get the actual completion date to pull in with 'complete'. The bold is what I added, but then it just pulls in the {actualCompletionDate} and not the date itself.

 

valueexpression=IF({milestone}.{name}="STRATEGY APPROVED",IF(CONTAINS("DED",{status}),"N/A",IF(CONTAINS("CPL",{status}),"Complete, {actualCompletionDate}",IF({plannedCompletionDate}<$$TODAY,CONCAT(" DUE ",{plannedCompletionDate}),CONCAT("🟢 DUE ",{plannedCompletionDate})))))

 

I feel like I'm missing a CONCAT somewhere?

Avatar

Level 6

Oh yep, you can just display text without a CONCAT, but to pull in any other field you def need that. What about....

 

valueexpression=
IF({milestone}.{name}="STRATEGY APPROVED",IF(CONTAINS("DED",{status}),"N/A",IF(CONTAINS("CPL",{status}),CONCAT("Complete ",{actualCompletionDate}),IF({plannedCompletionDate}<$$TODAY,CONCAT(" DUE ",{plannedCompletionDate}),CONCAT("🟢 DUE ",{plannedCompletionDate})))))

Avatar

Level 2

I got it to work with this: valueexpression=IF({milestone}.{name}="AUDITS RECEIVED",IF(AND({status}="DED","N/A", {plannedCompletionDate}<$$TODAY), " ",IF(AND({status}="DED","N/A", {plannedCompletionDate}>$$TODAY), "🟢 ",IF(CONTAINS("CPL",{status}), CONCAT("Completed ",{actualCompletionDate}),IF({plannedCompletionDate}<$$TODAY, CONCAT(" ",{plannedCompletionDate}),CONCAT("🟢 ",{plannedCompletionDate}))))))

 

Thank you for all your guidance!

Avatar

Community Advisor


Very nice @lindselib — thanks for sharing!


Pasting icons into calculated valueexpressions within an iterate using divs…gorgeous.

 

Regards,

Doug