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

[SarahSm]

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=

[William--]

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. 

[SarahSm]

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. 

[lindselib]

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]

• 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.

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.

[lindselib]

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

[J_Hobbs]

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

[lindselib]

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!

[J_Hobbs]

Thank you! 

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

🚴

[lindselib]

*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.

[Jcosta16]

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}))))))

[Jcosta16]

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?

[lindselib]

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

[Jcosta16]

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?

[lindselib]

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})))))

[Jcosta16]

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!

[Doug_Den_Hoed__AtAppStore]

Very nice @lindselib — thanks for sharing!

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

Regards,

Doug