Remove a task from a report if it contains a specific predecessor

[LaurensKr1]

Hi,

I am working on a task report and I want to remove tasks (filter them out) if the task has a specific predecessor on it. It filter seems to work well if the task has only 1 predecessor (the specific predecessor in question), but if the task has that specific predecessor and any additional predecessors, it is not filtering it out. I tried the below:

predecessorsMM:name=Name of predecessor
predecessorsMM:name_Mod=cinotcontains

[skyehansen · Accepted solution]

your error makes sense, and this is why EXISTS statements are such a powerful filter. What you have crafted above is called a collections filter. It's looking at your collection of predecessors in each task. The way it works is, if the task has more than one predecessor, Workfront will look at each predecessor separately when applying the filter. 

When you only have 1 predecessor, and this contains "Name of predecessor" then Workfront will exclude it.

When you have 2 predecessor, and one of them is "Not The Name You're Trying To Exclude", then Workfront will include the task because one of the predecessors fulfills your filter requirement and the other one does not, so the task can be included.

When you use EXISTS statements, you're in effect stating that in order to be included, NONE of the predecessors should have this. (I guess that makes it a NOTEXISTS statement, haha) Hopefully that clarifies the difference between the two types of filters for you?

The exists statement might look something like the below. I didn't test it -- so you should -- but I think it contains most of the components asked for. I copied the name filter from your code above.

You should probably check my spelling! 

EXISTS:a:$$EXISTSMOD=NOTEXISTS

EXISTS:a:$$OBJCODE=PRED

EXISTS:a:successorID=FIELD:ID

EXISTS:a:predecessor:name=Name of predecessor

EXISTS:a:predecessor:name_Mod=cicontains

Quick plug for the next EXISTS webinar below:

https://events.teams.microsoft.com/event/2699d8d3-233a-4f57-92f1-0e8bfaa01f1f@fa7b1b5a-7b34-4387-94a...

[LaurensKr1]

Hi Skye,

That is perfect and seems to be working.

A follow up question, can you use EXISTS filter on the same object? Meaning, if I am on a task report and I want to apply an EXISTS filter on the Task $$OBJCODE. 

[skyehansen]

in theory you could. But in practice it's more "how do you intend to use it?"

for example, the use case that comes immediately to mind is, "my task report is showing parent tasks, and I need to perform a query about its children". So the objcode for this would be task, and the linking line would have the other object's "parentID" being equal to the current report's (task) "ID". This is the clear way to say "I'm querying the children".

You're already demonstrating some understanding about collections, so I can also say here that obviously if you can get a collections filter about tasks going in your task report, you can also use an exists statement if you hit a limitation in your collections filter.