Conditional Formatting Based on Two Fields | Community
Skip to main content
K
KevinQu1
Level 10
November 13, 2020
Solved

Conditional Formatting Based on Two Fields

  • November 13, 2020
  • 3 replies
  • 1332 views

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

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by KevinQu1

    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

    3 replies

    imgrund
    Adobe Employee
    imgrundAdobe Employee
    Adobe Employee
    November 13, 2020

    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.

    K
    KevinQu1Author
    Level 10
    November 13, 2020

    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?

    K
    KevinQu1AuthorAccepted solution
    Level 10
    November 20, 2020

    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

    K
    KevinQu1Author
    Level 10
    November 20, 2020

    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
      Adobe Employee
      kynabaker16Adobe Employee
      Adobe Employee
      November 20, 2020

      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!

      Terms & ConditionsAccessibility statement

      Loading footer...