Expand my Community achievements bar.

Allow conditional formatting using more than one column - an AND statement.

Avatar

bfg
Level 2

11/8/23

Description - Allow conditional formatting using more than one column - an AND statement.

 

Why is this feature important to you - This seems like such a basic capability nowadays so I'm baffled as to why this cannot be done.  It's important because when you don't include the % complete column it highlights tasks that are complete, which makes no sense.  There are tons of other things someone might want to do and without the ability to write this kind of logic the conditional formatting is in my opinion almost useless or close to it.

 

How would you like the feature to work - I would like to create the following conditions and similar conditions:  The task planned completion date column is less than today, and the % complete column is less than 100%.

 

Current Behavior - Highlights tasks that are complete, which makes no sense.

 

I may not have worded this in the best manner, but hopefully what I'm suggesting will be understood.  I cannot be the first person to ask for this functionality.

5 Comments

Avatar

Community Advisor

11/8/23

I think you can already do this. That's probably why no one else has asked for it. Can you post a screenshot of exactly what you're asking for? You can just photoshop a screenshot or something so it's easily understandable.

Avatar

bfg
Level 2

11/8/23

@RandyRoberts

 

Tell me if this helps:

 

IF "Planned Completion Date" is LESS THAN $$TODAY AND "% Complete" is NOT 100% THEN "turn the row red" or some other conditional formatting of choice.

 

does that help, is that clear?

Avatar

Community Advisor

11/8/23

My first thought and easiest way would be a calc field on the object that says "IF "Planned Completion Date" is LESS THAN $$TODAY AND "% Complete" is NOT 100% THEN "TRUE"

Then a report column saying if My Calc Field = "True" then colour the row Red.

Still wondering if there's a way to put an AND in between column rules so they don't execute from the bottom up.

Avatar

Level 1

1/26/24

@RandyRoberts 

I was researching this a bit and thinking about doing this the way you suggested, with a calculated field.  I have exactly the same request as @bfg 

I read this article: 

https://experienceleague.adobe.com/docs/workfront/using/reporting/reports/calculated-custom-data/cal... 

LeeFo2_0-1706308879843.png

I'm a novice so I might be missing something here, but... If I understand this correctly, if you use a custom calculated field and you use $$TODAY, it will only recalculate if you trigger a recalculation or make a change to the object itself (the task), which means if you drop into a view and without updating tasks this field will not update and the row highlight based on it will not be accurate (it becomes out of date until you change something on the task, or retrigger the calculate on all tasks.  

 

I believe that you can achieve the setting of the fields to something like 'past due' within a task view.  For instance, I got this to work in the view to set add and calculate a custom column directly in the view: 

displayname=On Time
textmode=true
valueexpression=IF({plannedCompletionDate}>$$TODAY,"On Time",IF({percentComplete}<100,"Past Due","On Time"))
valueformat=HTML
It says that you can't use a value expression in a view with conditional formatting. 
LeeFo2_2-1706311173157.png

So, I have a task based field in my view that recalculates each time I open a project's list of tasks, however, I can't use that field to highlight the row (or just the planned completion date) in red to highlight that the task is past due. 


If you found a way to make this happen @bfg could you share?  If I'm missing something here, please let me know @RandyRoberts.

 

Thanks for your help!  

Avatar

Level 4

2/22/24

Order of operations would help here. Create a rule that checks for % complete (or status = complete) first and set the formatting to white (or whatever your standard view should be.)

 

Then create your rule for highlighting the due date.

 

If the system finds a rule to be "TRUE" it stops at that point, so it would not do the due date highlighting if the "% complete" rule has already been applied.

 

BUT, I 100% agree that there should be the ability to write "AND" statements for conditional formatting. For all of us database nerds, it would make much more sense!