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=
Views
Replies
Total Likes
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.
Views
Replies
Total Likes
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.
Views
Replies
Total Likes
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
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.
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.
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 |
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(" |
valueformat=HTML |
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
Views
Replies
Total Likes
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!
Thank you!
---------------------------------------------
Views
Replies
Total Likes
*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.
Views
Replies
Total Likes
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()), "
Views
Replies
Total Likes
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), "
Any ideas on where I'm going wrong for the overdue milestones?
Views
Replies
Total Likes
Hmmm maybe try.....
valueexpression=IF({milestone}.{name}="AUDITS RECEIVED",IF(CONTAINS("DED",{status}),"N/A",IF({plannedCompletionDate}<$$TODAY,CONCAT("
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("
Hope that helps! I think the crux of it is you need to reference the task status with that "contains" statement.
Thanks,
Lindsey
Views
Replies
Total Likes
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("
I feel like I'm missing a CONCAT somewhere?
Views
Replies
Total Likes
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})))))
Views
Replies
Total Likes
I got it to work with this: valueexpression=IF({milestone}.{name}="AUDITS RECEIVED",IF(AND({status}="DED","N/A", {plannedCompletionDate}<$$TODAY), "
Thank you for all your guidance!
Very nice @lindselib — thanks for sharing!
Pasting icons into calculated valueexpressions within an iterate using divs…gorgeous.
Regards,
Doug
Views
Replies
Total Likes
Views
Likes
Replies