Conditional Formatting Based on Two Fields

[CalvinFold]

I am just having a mental block on this one…

I have two fields on an assignment report:

1. workRequired (hours assigned to user on a task)
2. actualWorkCompleted (hours logged by that assigned user against that task)

These work fine. But I am trying to conditionally format the actualWorkCompleted value bold and red if it is greater than workRequired. But despite trying a bunch of different combinations both in the Advanced area and going into text mode, I can't get the bold/red to work.

You'll note a blue field behind this column (case.0), which is basically a blue field behind the entire column regardless of value.

I'm guessing I'm missing some key point to get this to work?

displayname=TEST

linkedname=direct

namekey=actualWorkCompleted

querysort=actualWorkCompleted

styledef.case.0.comparison.icon=false

styledef.case.0.comparison.leftmethod=status

styledef.case.0.comparison.lefttext=status

styledef.case.0.comparison.operator=notblank

styledef.case.0.comparison.righttext=

styledef.case.0.comparison.trueproperty.0.name=bgcolor

styledef.case.0.comparison.trueproperty.0.value=DBF3FA

styledef.case.0.comparison.truetext=

styledef.case.1.comparison.icon=false

styledef.case.1.comparison.leftmethod=actualWorkCompleted

styledef.case.1.comparison.lefttext=actualWorkCompleted

styledef.case.1.comparison.operator=gt

styledef.case.1.comparison.operatortype=double

styledef.case.1.comparison.righttext=workRequired

styledef.case.1.comparison.trueproperty.0.name=fontstyle

styledef.case.1.comparison.trueproperty.0.value=bold

styledef.case.1.comparison.trueproperty.1.name=textcolor

styledef.case.1.comparison.trueproperty.1.value=d30519

styledef.case.1.comparison.truetext=

textmode=true

valuefield=actualWorkCompleted

valueformat=compound

viewalias=actualWorkCompleted

[AnthonyIm]

Hi - Do you know how actualWorkCompleted is stored?

I know that workRequired is actually stored in minutes. So I'm wondering if the issue is you are comparing 60 workRequired to 1 actualWorkCompleted so your Actual is never greater than what is required.

[CalvinFold]

So I'd basically have to create a new custom field to get workRequired to hours, then do the comparison with that?

Can you verify the rest of my syntax is valid?

[CalvinFold · Accepted solution]

With the help of Support, ended-up with this:

displayname=Hours Logged by Me

linkedname=direct

namekey=actualWorkCompleted

querysort=actualWorkCompleted

styledef.case.0.comparison.icon=false

styledef.case.0.comparison.leftmethod=status

styledef.case.0.comparison.lefttext=status

styledef.case.0.comparison.operator=notblank

styledef.case.0.comparison.righttext=

styledef.case.0.comparison.trueproperty.0.name=bgcolor

styledef.case.0.comparison.trueproperty.0.value=DBF3FA

styledef.case.0.comparison.truetext=

styledef.case.1.comparison.icon=false

styledef.case.1.comparison.leftmethod=actualWorkCompleted

styledef.case.1.comparison.lefttext=actualWorkCompleted

styledef.case.1.comparison.operator=gt

styledef.case.1.comparison.operatortype=int

styledef.case.1.comparison.rightmethod=workRequired

styledef.case.1.comparison.righttext=workRequired

styledef.case.1.comparison.trueproperty.0.name=fontstyle

styledef.case.1.comparison.trueproperty.0.value=bold

styledef.case.1.comparison.trueproperty.1.name=textcolor

styledef.case.1.comparison.trueproperty.1.value=d30519

styledef.case.1.comparison.truetext=

textmode=true

valuefield=actualWorkCompleted

valueformat=compound

viewalias=actualWorkCompleted

[CalvinFold]

An item of note:

It appears Text Mode is subject to issues caused by "gremlins."

While working via Zoom chat with Support, code copy-n-pasted there, then pasted into Text Mode appeared to lose its carriage returns. I'd add them back in, the code didn't work.

I happened to paste the code to a Mac sticky note and noticed some lines were single space, some were double. Ran the text through BBEdit to "Zap Gremlins" and then back into Text Mode and…the visually same code worked!

For those who aren't coders, a gremlin in this context is an invisible character, sometimes known as a control code, that comes along for the ride when copy-n-pasting. I find Microsoft apps are the worst (Word and Excel often allow/add gremlins during a copy-n-paste). Tough to "see" without software like BBEdit or using InDesign and viewing the invisible characters.

This particular case was probably different apps using different invisible control codes for "end of line" (carriage return); there are at least three types of EOL if memory serves. Support may have been on a PC, I am on a Mac, and Zoom may make certain assumptions or changes on its end.

Bane of any coder's existence these invisible little annoyances, and it appears Workfront is no different…

[kynabaker16]

Oooh, now that is interesting! I have always wondered why things get messed up when copy/pasting from one app to another... I've never called it a gremlin though! Thank you for sharing your answer!

[AnthonyIm]

When coming from a Word doc or PPT deck, I always paste into Notepad first and then into Workfront.

Looking at your code, it looks like you were able to compare to the out of the box Work Required field. Is that correct?

[CalvinFold]

Yeah, no funky conversion from minutes to hours or anything like that; they compared directly.

The tricky bit was "actualWorkCompleted" which is in the API Explorer (which I forgot to check…); that value isn't in the standard builder UI.

Well, and the darned gremlins. ;-p