Need help adding conditional formatting to a custom column with a custom calculated valueexpression | Community
Skip to main content
K
KyleFr2
May 9, 2024
Solved

Need help adding conditional formatting to a custom column with a custom calculated valueexpression

  • May 9, 2024
  • 1 reply
  • 769 views

So essentially I created a valueexpression that calculates the difference between the entry date and the actual completion date and returns a "## days" copy. I'm hoping to add custom formatting (cell color and text color) for any returns greater than both 30 and 90 days. I can't figure out how to align the comparison left and right text to return that outcome. Here is the code I have so far:

 

displayname=Entry-Actual Completion Date
linkedname=direct
querysort=plannedCompletionDate
styledef.case.1.comparison.icon=false
styledef.case.1.comparison.leftmethod=direct
styledef.case.1.comparison.lefttext=direct
styledef.case.1.comparison.operator=gt
styledef.case.1.comparison.operatortype=date
styledef.case.1.comparison.righttext=90 Days
styledef.case.1.comparison.trueproperty.0.name=textcolor
styledef.case.1.comparison.trueproperty.0.value=d30519
styledef.case.1.comparison.trueproperty.1.name=bgcolor
styledef.case.1.comparison.trueproperty.1.value=eac6c9
styledef.case.1.comparison.trueproperty.2.name=align
styledef.case.1.comparison.trueproperty.2.value=left
styledef.case.1.comparison.truetext=
styledef.case.2.comparison.icon=false
styledef.case.2.comparison.leftmethod=direct
styledef.case.2.comparison.lefttext=direct
styledef.case.2.comparison.operator=gt
styledef.case.2.comparison.operatortype=date
styledef.case.2.comparison.righttext=30 Days
styledef.case.2.comparison.trueproperty.0.name=textcolor
styledef.case.2.comparison.trueproperty.0.value=e19503
styledef.case.2.comparison.trueproperty.1.name=bgcolor
styledef.case.2.comparison.trueproperty.1.value=feecc8
styledef.case.2.comparison.truetext=
textmode=true
valueexpression=CONCAT(ROUND(DIV(DATEDIFF({actualCompletionDate},{entryDate}),2),0)," Days")
valueformat=HTML

    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 skyehansen

    ah ok. That explains it. I'm sure this isn't right but my brain always translates the "leftmethod" and "lefttext" stuff as "here's the field we're using on the left" and so I was a bit surprised to see that "direct" might work (I guess it doesn't, lol). And so that's why I'm also recommending a calculated field -- probably the best way to get something that works in that space so you can do some formatting to see if you want to commit to that kind of thing.

    1 reply

    skyehansen
    Community Advisor
    skyehansenCommunity Advisor
    May 9, 2024

    I'd be interested to hear how you came up with this, especially the leftmethod and lefttext lines. Can I instead talk you into putting a calculated field on all your objects and doing your calculation in there, and formatting it as a number? (leave off the days text for the time being... maybe if you can get the field comparison to work, you can put in a valueexpression to add " days")

    K
    KyleFr2Author
    May 9, 2024

    Hi Skye,

     

    I apologize, that was an earlier attempt to try and work out the issue, I pulled the "direct" from the linkedname field. I'll look into the calculation option and see if I can find a workaround, otherwise it's not the end of the world if that data can't have rules and formatting. Thanks!

    skyehansen
    Community Advisor
    skyehansenCommunity AdvisorAccepted solution
    May 9, 2024

    ah ok. That explains it. I'm sure this isn't right but my brain always translates the "leftmethod" and "lefttext" stuff as "here's the field we're using on the left" and so I was a bit surprised to see that "direct" might work (I guess it doesn't, lol). And so that's why I'm also recommending a calculated field -- probably the best way to get something that works in that space so you can do some formatting to see if you want to commit to that kind of thing.

    Terms & ConditionsAccessibility statement

    Loading footer...