Expand my Community achievements bar.

SOLVED

Using 'contains' in collection reports

Avatar

Level 2

Hi! Was hoping to see if this group could help me with some text mode - Trying to pull in a task name, but I can only pull it in if it' EXACTLY what's in the quotes - is there a contains feature I could use instead, so that if the task contains 'Transmittal T1' the rest doesn't matter?

 

valueexpression=IF({name}="Transmittal T1 - Client Feedback Due",CONCAT({actualCompletionDate}))

 

Additionally, is there a formula I could use to show projected completion date if an actual does not exist? I'd like it to pull either/or, and then note which one it is. Thank you!

1 Accepted Solution

Avatar

Correct answer by
Level 4

Yep, it's actually CONTAINS

 

CONTAINSThis expression returns true if the findText string is found within the withinText string and is formatted as follows:
CONTAINS(findText, withinText)

 

So: IF(CONTAINS("Transmittal T1",{name}),CONCAT({actualCompletionDate}))

View solution in original post

10 Replies

Avatar

Community Advisor

I would give this a try, let me know if it doesn't work!

 

 

valueexpression=IF(CONTAINS(“Transmittal T1",{name}),CONCAT({actualCompletionDate}),"")

 

 

​On your second question, wouldn't Actual Completion Date always exist...? I can't think of an object in Workfront where it wouldn't

Avatar

Level 2

I meant if there's no actual completion date yet, can we show the projected completion date? 

Avatar

Community Advisor

Ah, I had Planned Completion Date on the brain - my bad! I'm not positive on that part of the text mode (might want to post a new thread with just that text mode being requested to get a fresh set of eyes on it)

Avatar

Community Advisor

Just replace the actual with planned:
IF(CONTAINS("Transmittal T1",{name}),CONCAT({plannedCompletionDate}))

If this helped you, please mark correct to help others : )

Avatar

Community Advisor

yes, put this in instead of actual completion date alone

IF(!ISBLANK({actualCompletionDate})="true",{actualCompletionDate},{projectedCompletionDate})

 

Avatar

Level 2

Ok - still having a bit of trouble! Here's the full text mode I'm using to pull in contains 'prepare final review'. See the screenshot to see that it's complete, but the report is still showing the column blank. I also tried to pull in 'final client edits' which was also blank, but I was wondering if that would pull in multiple actual completion dates since there are 4 tasks (+1 parent task) with that in the name?

 

displayname=Contains prepare final review
listdelimiter=<p>
listmethod=nested(tasks).lists
textmode=true
type=iterate
valueexpression=IF(CONTAINS(Prepare Final Review",{name}),CONCAT({actualCompletionDate}),"")
valueformat=HTML

Avatar

Community Advisor

Looks like you're missing a " in the value expression, right before Prepare

valueexpression=IF(CONTAINS("Prepare Final Review",{name}),CONCAT({actualCompletionDate}),"")

Avatar

Correct answer by
Level 4

Yep, it's actually CONTAINS

 

CONTAINSThis expression returns true if the findText string is found within the withinText string and is formatted as follows:
CONTAINS(findText, withinText)

 

So: IF(CONTAINS("Transmittal T1",{name}),CONCAT({actualCompletionDate}))