Solved

Problems getting desired results using LEFT text expression

Forum|Forum|10 months ago
March 7, 2025
2 replies
583 views

I've been asked to show a date in a report as

Sat, 3/8/25

However, from what I see I can only show

Sat, Mar 8, 2025 or 3/8/25 (without the day)

To workaround that limitation, I want to only show the first 5 characters from the partialAtDate - so "Sat, "

Then I'll use sharecol to show the date ( format atDate) after that, so 3/8/25

The text mode I'm using to only display the first five characters from the left is below, but the report isn't showing anything in the column. Can someone tell me what I'm overlooking, please?

linkedname=direct
namekey=plannedCompletionDate
querysort=plannedCompletionDate
valueexpression=LEFT({plannedCompletionDate},5)
valueformat=partialAtDate

If there a better way to get to my desired result, I'm all ears!

Thank you.

Best answer by skyehansen

I'm not the best on understanding text mode, but in my limited understanding, I feel that the problem is in your order of operations. Your valueexpression is going to run first and then the valueformat is going to run after this.

For example, if the date is 3/1/25, your valueexpression will turn this into "3/1/2" and then your valueformat will go "well... that's not a date" and that's where you get a blank.

I would instead take a look at the "CASE" portion of the Calculated Data Expressions documentation.

I was going to say look at CASE and DAYOFWEEK, but there's no need, because the CASE example includes the DAYOFWEEK calculation. You can literally just copy and paste that calculation, substituting planned completion date instead of entry date, and using "Sun, ", "Mon, " etc., instead of "Sunday", "Monday" etc.

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

(And frankly, instead of doing a sharecol, you should try and build the whole thing in one column as this will keep things tidier. But sharecol will probably work as well.)

skyehansen

Accepted solution

Community Advisor

I'm not the best on understanding text mode, but in my limited understanding, I feel that the problem is in your order of operations. Your valueexpression is going to run first and then the valueformat is going to run after this.

For example, if the date is 3/1/25, your valueexpression will turn this into "3/1/2" and then your valueformat will go "well... that's not a date" and that's where you get a blank.

I would instead take a look at the "CASE" portion of the Calculated Data Expressions documentation.

I was going to say look at CASE and DAYOFWEEK, but there's no need, because the CASE example includes the DAYOFWEEK calculation. You can literally just copy and paste that calculation, substituting planned completion date instead of entry date, and using "Sun, ", "Mon, " etc., instead of "Sunday", "Monday" etc.

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

(And frankly, instead of doing a sharecol, you should try and build the whole thing in one column as this will keep things tidier. But sharecol will probably work as well.)

C

CatherineKeAuthor

Thank you, Skye! I was able to use CASE, and concatenated the day of week and the date (Date Needed) in a column:

valueexpression=CONCAT(CASE(DAYOFWEEK({project}.{DE:Date Needed}),"Sun","Mon","Tue","Wed","Thu","Fri","Sat") ,", ",({project}.{DE:Date Needed}))
valueformat=HTML

However, it never stays that simple. The original report had a column with the Date Needed, and styling. It shows the entire rows in red if it's "late" (Date Needed is less than the plannedCompletionDate):

displayname=Project Due Date
linkedname=project
namekey=view.relatedcolumn
namekeyargkey.0=project
namekeyargkey.1=Date Needed
querysort=DE:project:Date Needed
row.0.styledef.applyallcases=true
row.0.styledef.case.0.comparison.icon=false
row.0.styledef.case.0.comparison.isrowcase=true
row.0.styledef.case.0.comparison.leftmethod=DE:project:Date Needed
row.0.styledef.case.0.comparison.lefttext=DE:project:Date Needed
row.0.styledef.case.0.comparison.operator=lt
row.0.styledef.case.0.comparison.operatortype=date
row.0.styledef.case.0.comparison.rightmethod=plannedCompletionDate
row.0.styledef.case.0.comparison.righttext=plannedCompletionDate
row.0.styledef.case.0.comparison.trueproperty.0.name=bgcolor
row.0.styledef.case.0.comparison.trueproperty.0.value=eac6c9
row.0.styledef.case.0.comparison.truetext=
sortOrder=1
sortType=asc
styledef.case.0.comparison.icon=false
styledef.case.0.comparison.isrowcase=true
styledef.case.0.comparison.leftmethod=DE:project:Date Needed
styledef.case.0.comparison.lefttext=DE:project:Date Needed
styledef.case.0.comparison.operator=lt
styledef.case.0.comparison.operatortype=date
styledef.case.0.comparison.rightmethod=plannedCompletionDate
styledef.case.0.comparison.righttext=plannedCompletionDate
styledef.case.0.comparison.trueproperty.0.name=bgcolor
styledef.case.0.comparison.trueproperty.0.value=eac6c9
styledef.case.0.comparison.truetext=
valuefield=project:Date Needed
valueformat=customDateAsString

If I try to change the last two rows, valuefield and valueformat, to the following, my row styling is gone, and only fields in this column are showing the style (red if late).

valueexpression=CONCAT(CASE(DAYOFWEEK({project}.{DE:Date Needed}),"Sun","Mon","Tue","Wed","Thu","Fri","Sat") ,", ",({project}.{DE:Date Needed}))
valueformat=HTML

The row.0. info from the original report is gone, and if I try to add it back it doesn't save.

Am I missing another change that would be needed?

Full disclosure - I'm working with what was already there, and don't have a grasp on how the styledef, row.0.styledef works...

skyehansen

Community Advisor

I really recommend you think of this as a completely separate question. The reason being that there is usually more than one column to a table report, and if you need to have the entire row "light up in red" you could literally put it on any other (simpler) column than what you've got going on here.

Please therefore, "go back to the drawing board" -- read up on conditional formatting via comparing field values in this article, and rebuild your formatting code from scratch in a simple column with not that much going on -- it will be easier for everyone concerned. i.e. This way, if you should have any more specific questions, you can ask them in that context. Otherwise, if you try and frankenstein code in from an older version of the report into a column with a valueexpression, it will be more difficult for someone to try and help you with that (too much going on to identify the problem).

Sven-iX

Community Advisor

@skyehansen is right - your valueformat=partialAtDate is the problem: Your valueexpression is, by definition, a string (since LEFT is a string function).

Change it to valueformat=HTML and you'll see the left 5 chars.

That said - I second Skye's advise to use the CASE/DAYOFWEEK example from the documentation page.

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded