hi Katie, you should read up on collections.
https://experienceleague.adobe.com/docs/workfront/using/reporting/reports/text-mode/reference-collections-report.html?lang=en
Understanding the concept of "collections" is pretty critical to your working knowledge of workfront. At its simplest, this means understanding that a project can contain more than one task, issue, document, project user, and so on (i.e. a collection of tasks, issues and so on). And it means understanding that if you are referencing (looking for answers in) a collection of some sort, certain things will either not work, or need a little finagling.
Calculated fields are one place where referencing collections won't work. So, you won't be able to create a calculated field where the calculation references a collection of tasks and then pulls out your Archive task date. So it's imperative that you understand how to reference a collection from a report -- this will make your reporting more flexible.
With that said, here is some sample textmode below.
displayname=Archive Date
listdelimiter=<p>
listmethod=nested(project.tasks).lists
textmode=true
type=iterate
valueexpression=IF(CONTAINS('Archive',{name}),IFIN({status},"CPL", "DED",CONCAT("Completed ",{actualCompletionDate}),CONCAT("Due ",{plannedCompletionDate})))
valueformat=HTML
If you compare it to the "Add a collection column in a report View" section of the article I sent you to, you'll see I'm following the standard syntax, and have made two changes.
1) the collection I am referencing is the "project.tasks" collection
2) the valueexpression I'm using states that if the name of the project.task contains "Archive", then if the task is complete/canceled, the cell will display "Completed <actual completion date>", otherwise it will display "Due <planned completion date>" -- this is a commonly used piece of text mode that I pretty much copy from one report to the next.
Hope this is helpful.
