Expand my Community achievements bar.

Expression Based Conditional Formatting?

Avatar

Level 3
Does anyone know if there is a way to achieve expression based conditional formatting? The only way I'm aware of to get there is to add the expression as a custom field and create the condition based off the custom field. However, the ability to do this on the fly within the report (without the use of a custom field) would be EXTREMELY helpful. Has anyone come across a creative workaround?
Topics

Topics help categorize Community content and increase your ability to discover relevant content.

9 Replies

Avatar

Level 10
We create custom expressions in a form and then use that new attribute. Couldn’t think of any other way to accomplish that. WorkFront consulting helped us arrive at that solution and implement it. Eric

Avatar

Level 3
Thanks, Eric. Yes, the custom field option is the only thing I've found so far as well. If that was what remote consulting came up with for you, then I'm thinking it's unlikely there is another solution. I'm still hopeful, though, that maybe someone has found a way to trick the system....or that maybe someone from Workfront knows of an enhancement in the works. :-)

Avatar

Community Advisor
Hi Kathy. Yes, I've had some success doing dynamic formatting of on the fly -- in particular, highlighting rows in green, then yellow, then red as a Service Level Agreement (SLA) deadline approached on an open issue. Click here for an example. Regards, Doug

Avatar

Level 3
Hi Doug, I’ve gotten conditional formatting to work as well, just never when what I’m comparing against is the outcome of an expression. Since I can’t see what’s going on in the background of your example, were you using an expression (as opposed to a field) as the basis for the colors? So for example, if I wanted to highlight any row where the first four characters of the milestone path name are “2016” (LEFT({milestonePath}.{name},4)…..just as a random example), would this be possible? If so, would you be willing to share the text mode syntax of an example where you’ve gotten this to work? Thanks again, Kathy

Avatar

Community Advisor
Hi Kathy, In my example, the data pertains to imminent due dates (eg "required response dates to" and "resolution dates of" Helpdesk Issues), so I am comparing against "$$NOW", and highlighting in yellow as the due date approaches, and red as it arrives. Here is a snippit of the red rule: styledef.case.3.comparison.icon=false styledef.case.3.comparison.leftmethod=DE:ESP SLA Response Yellow styledef.case.3.comparison.lefttext=DE:ESP SLA Response Yellow styledef.case.3.comparison.operator=lt styledef.case.3.comparison.operatortype=date styledef.case.3.comparison.righttext=$$NOW styledef.case.3.comparison.trueproperty.0.name=bgcolor styledef.case.3.comparison.trueproperty.0.value=FFFF8E styledef.case.3.comparison.truetext= Similarly (although it would be a cheat), perhaps you could coerce the values you are interested in evaluating into dates so that you could compare against $$NOW. Regards, Doug

Avatar

Level 10
If I understand correctly, you are looking to use an expression in place of something like: styledef.case.3.comparison.righttext=$$NOW For example, you'd like something like this: styledef.case.3.comparison.righttext=IF({actualStartDate}>{plannedStartDate},"Started Late","Started On-Time") Now I don't think what I've entered above would work. But, I think this is what you're looking for right?

Avatar

Level 3
Thanks, Narayan. Yes, that's exactly it, although there are times when it's not even dates I'm dealing with; it may be strings. I've tried a bunch of different combinations of things in text mode, but I can't seem to get anything to work. It just seems very limiting to have to add more fields any time you want something a little more complex highlighted on a report.

Avatar

Level 3
While this doesn’t solve the original issue I posted, I figured I would share a strategy that seems to work for date field to date field comparisons. You may know that when you are setting up a conditional formatting rule, that you can sometimes type FIELD: in the right side of the rule, save it and come back in to find a field selection box. However, when I’ve had a date field on the left side, it won’t take the “FIELD:” option. I’ve been able to coerce this to work by selecting something other than a date (like “Name”), saving and getting back in to see the field to field comparison GUI. At this point, you can select whatever you want on either the right or left. That’s all well and good, but once you select two dates, the result still doesn’t display with the proper formatting. I found that if you then go into text mode, you need to manually add a “rightmethod” line with the field name used for the right side of the comparison (as shown below), and then it works. Go figure. I’m not sure why this doesn’t get added automatically. Anyway, I figured I’d share that small victory. Now if only I can stumble across the magic pixy dust that allows you to use an expression rather than a field as the foundation for the formatting (sigh)… displayname=Test styledef.case.0.comparison.icon=false styledef.case.0.comparison.leftmethod=actualCompletionDate styledef.case.0.comparison.lefttext=actualCompletionDate styledef.case.0.comparison.operator=lte styledef.case.0.comparison.operatortype=date styledef.case.0.comparison.rightmethod=defaultBaselineTask:plannedCompletionDate styledef.case.0.comparison.righttext=FIELD:defaultBaselineTask:plannedCompletionDate styledef.case.0.comparison.trueproperty.0.name=bgcolor styledef.case.0.comparison.trueproperty.0.value=def6e2 styledef.case.0.comparison.truetext= textmode=true valuefield=actualCompletionDate valueformat=atDate

Avatar

Community Advisor
Kathy, congratulations on your small victory! Persistence triumphs again. Regards, Doug