List of projects filtered by tasks that meet two conditions | Community
Skip to main content
JenL-LAZ
JenL-LAZ
Level 2
August 27, 2025
Solved

List of projects filtered by tasks that meet two conditions

  • August 27, 2025
  • 2 replies
  • 379 views

Hi, I'm trying to create a report that shows a list of projects where both of these conditions are true (using fake ID numbers for the sake of this example):

  • The status of template task ID AAAAA12345 is not Rejected.
  • The status of template task ID 54321ZZZZZ is not Rejected.

I know how to filter for each of these in a task report using the standard filter mode, but I only want to see the project name and not the tasks themselves. I suspect this requires using text mode for the filter within a project report, but I'm newer to text mode and I'm having a hard time figuring it out. How can I create this report?

Best answer by JustinRenteria2

I was only able to accomplish this using an Exists filter. 

AND:A:EXISTS:A:$$EXISTSMOD=EXISTS
AND:A:EXISTS:A:$$OBJCODE=TASK
AND:A:EXISTS:A:projectID=FIELD:ID
AND:A:EXISTS:A:status=AFN
AND:A:EXISTS:A:status_Mod=notin
AND:A:EXISTS:A:templateTaskID=AAAAA12345
AND:A:EXISTS:A:templateTaskID_Mod=in
EXISTS:A:$$EXISTSMOD=EXISTS
EXISTS:A:$$OBJCODE=TASK
EXISTS:A:projectID=FIELD:ID
EXISTS:A:status=AFN
EXISTS:A:status_Mod=notin
EXISTS:A:templateTaskID=54321ZZZZZ
EXISTS:A:templateTaskID_Mod=in

2 replies

JenL-LAZ
JenL-LAZAuthor
Level 2
August 28, 2025

Here's what I have so far. Each of these filters works on its own within a project report, but joining them with AND:1: isn't working.

tasks:status=AFN tasks:status_Mod=notin tasks:templateTaskID=AAAAA12345 tasks:templateTaskID_Mod=in AND:1:tasks:status=AFN AND:1:tasks:status_Mod=notin AND:1:tasks:templateTaskID=54321ZZZZZ AND:1:tasks:templateTaskID_Mod=in
JustinRenteria2
JustinRenteria2Accepted solution
Level 6
August 28, 2025

I was only able to accomplish this using an Exists filter. 

AND:A:EXISTS:A:$$EXISTSMOD=EXISTS
AND:A:EXISTS:A:$$OBJCODE=TASK
AND:A:EXISTS:A:projectID=FIELD:ID
AND:A:EXISTS:A:status=AFN
AND:A:EXISTS:A:status_Mod=notin
AND:A:EXISTS:A:templateTaskID=AAAAA12345
AND:A:EXISTS:A:templateTaskID_Mod=in
EXISTS:A:$$EXISTSMOD=EXISTS
EXISTS:A:$$OBJCODE=TASK
EXISTS:A:projectID=FIELD:ID
EXISTS:A:status=AFN
EXISTS:A:status_Mod=notin
EXISTS:A:templateTaskID=54321ZZZZZ
EXISTS:A:templateTaskID_Mod=in
JenL-LAZ
JenL-LAZAuthor
Level 2
August 28, 2025

That worked! Thank you! I suspected it might need the EXISTS statement but I'm not that familiar with it yet. I'm also trying to figure out how to add one more condition to the filter: If the status of template task ID YYYYYYYYYY is Closed. How do I modify the code to add this last condition? I've tried versions using AND:B or AND:A:EXISTS:B: for this additional section but it's not working.

JenL-LAZ
JenL-LAZAuthor
Level 2
August 28, 2025

You have to use:
AND:B:EXISTS:A

That condition should look a little like this. Just sub your Template Task ID, and the three letter Status Key.

AND:B:EXISTS:A:$$EXISTSMOD=EXISTS
AND:B:EXISTS:A:$$OBJCODE=TASK
AND:B:EXISTS:A:projectID=FIELD:ID
AND:B:EXISTS:A:status=Your 3 letter status key
AND:B:EXISTS:A:status_Mod=in
AND:B:EXISTS:A:templateTaskID=YYYYYYYYYY
AND:B:EXISTS:A:templateTaskID_Mod=in

 


Ugh, I swear I tried that and it was only bringing up the template tasks that weren't complete, but your text snippet is working. Thank you so much, Justin!

    Terms & ConditionsAccessibility statement

    Loading footer...