Expand my Community achievements bar.

I am creating a project report and trying to sort by a column that utilizes a valueexpression from a task name. I can't get the sort to work properly.

Avatar

Level 5

The project report includes a “Due On” column, which is the Project, Planned Completion Date.

My text mode column is called “Plnd Live Testing,” which shows a date for tasks with specific names.

Both the task and project appear to have a Field Name of plannedCompletionDate.

I want to sort first by Plnd Live Testing, and second by Due On. It appears that it is only sorting by the project “due on” Project Planned Completion Date.

This is my text mode of the Plnd Live Testing column:

displayname=Plnd Live Testing

listdelimiter=<div>

listmethod=nested(tasks).lists

querysort=plannedCompletionDate

sortOrder=1

sortType=asc

textmode=true

type=iterate

valueexpression=IF({name}="Live QA Review"||{name}="Immediate Live QA Review"||{name}="Send Email",{plannedCompletionDate})

valueformat=HTML

0694X00000DToAUQA1.jpg

Topics

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

8 Replies

Avatar

Level 10

I will admit, I have never seen || in an IF statement before. I tend to do multiple IF statements. Something like

IF({name}="Live QA Review",{plannedCompletionDate},IF({name}="Immediate Live QA Review",{plannedCompletionDate},IF({name}="Send Email",{plannedCompletionDate},"")

Also, I see you have sorting on, just know that you can't sort collections. So that will sort your project by their planned completion date, not the dates of your tasks. :(

Avatar

Level 5

Thanks, Anthony!

To make sure I'm following you about not being able to sort by a collection... is this a collection because of my multiple IF statements (Live QA Review, Immediate Live QA Review, etc), or is it a collection simply because I have even one IF statement and/or "listmethod=nested(tasks).lists"?

Avatar

Level 10

It is a collection because it is a many to one relationship. So in this case, you are on a project report and a project can have multiple tasks. Same thing if you wanted to bring in issue or document data into the report as well. (Versus something like portfolio info which is a "reference" as it is a one-to-one relationship.)

A good indicator it is a collection though is the use of "listmethod=nested(tasks).lists". ;)

If your team really wants sorting for collections and/or conditional formatting @Doug Den Hoed‚ has a great magic reports tool that can do that. He helped out my last company with a hotsheet where he could sort and highlight overdue tasks in a project report.

Avatar

Community Advisor

Thanks for the tag, @Anthony Imgrund‚ (and well done on keeping your previous identity in one.workfront.com during your transition, btw).

In a related story, Cathy...until about 8 hours ago, I would have replied with my fervent agreement with my esteemed colleague that collections cannot be sorted, but in the edge case where there is an expected sort (e.g. by "known Task Name"), using a technique I developed yesterday, I might in fact have a way to sort collections, and am interested in working through a proof of concept.

If you (or Anthony, or others reading) have such an edge case and would be interested in joining me to do so, I invite you to drop me a line here, or via doug.denhoed@atappstore.com

Regards,

Doug

That is very intriguing, @Doug Den Hoed‚! I'm interested to see if we can make this happen. I'll reply to your email address with mine. Looking forward to hearing from you.

Thanks!

Avatar

Community Advisor

@Cathy Kenny‚ , Ask Doug to tell you about the IFIN statement while you're learning about sorting a collection. It's pretty cool.

@Doug Den Hoed‚ IDK if this case fits, but we are calling a collection of Tasks on a project to check if the prices are set, it looks something like this:

displayname=Tasks

listdelimiter=<p>

listmethod=nested(tasks)

textmode=true

type=iterate

usewidths=true

valueexpression=SORTASCSTRING(CONCAT({name}," - EUR ",{plannedRevenue}," - Hours: ",{workRequired}))

valueformat=HTML

width=500

What would be great of course, is to get the tasks sorted by Name :-)

Avatar

Community Advisor

Hi Chris,

Cathy, Anthony (and Scott) and I met last week and noodle this out to the extreme, during which I recorded raw footage I'll boil down into a highlight reel and share in due course. I chatted with several folks during Adobe Summit 2021 this week on Braindates around this same topic too (which was fun and chock full of messy technical flavor crystals).

For your case, depending on how many particular tasks you have (e.g. "these specifically named 10 tasks", or "all Tasks on a Project that never has more than 20 Tasks in total), it might be a good (albeit "heavy") fit...noting that as it sounds like you're looking for for only Tasks that meet a certain condition (e.g. prices set; or perhaps NOT set), it might be a great fit (and "lighter").

Regards,

Doug