Help with my EXISTS/NOTEXISTS statement

[-Jaye]

Hi all, I'm trying to build a filter on a project report to show all projects where users with the Design role have completed all their steps. So effectively, "all projects where Design is done their part, even if the project is still running". My thinking with the filter was to call in all projects where exists a Designer assignment, then filter out all projects where the Designer has said they're done on all their tasks (including multi-assigned tasks where the non-Design user is still working on them).

Here's what I have so far:

EXISTS:A:$$OBJCODE=ASSGN
EXISTS:A:assignedTo:roleID=605e087c00740f47158a61811b214ef2
EXISTS:A:assignedTo:roleID_Mod=in
EXISTS:A:projectID=FIELD:ID

AND:1:EXISTS:A:$$EXISTSMOD=NOTEXISTS
AND:1:EXISTS:A:$$OBJCODE=ASSGN
AND:1:EXISTS:A:assignedTo:roleID=605e087c00740f47158a61811b214ef2
AND:1:EXISTS:A:assignedTo:roleID_Mod=in
AND:1:EXISTS:A:projectID=FIELD:ID
AND:1:EXISTS:A:status=AA	AD
AND:1:EXISTS:A:status_Mod=in

plannedCompletionDate=$$TODAYby
plannedCompletionDate_Mod=between
plannedCompletionDate_Range=$$TODAYey
portfolioID=6054ad91001a2963e92229aeb0af6de4
portfolioID_Mod=notin

Using this filter, there are some projects missing that I would expect to find on the report. Can anyone spot what I'm missing? Thanks in advance!

[skyehansen · Accepted solution]

Without getting too much into it, I'm wondering if you'd be able to replace your first exists statement with a project users filter instead.

projectUsersMM:roleID=605e087c00740f47158a61811b214ef2
projectUsersMM:roleID_Mod=in

EXISTS:A:$$EXISTSMOD=NOTEXISTS
EXISTS:A:$$OBJCODE=ASSGN
EXISTS:A:assignedTo:roleID=605e087c00740f47158a61811b214ef2
EXISTS:A:assignedTo:roleID_Mod=in
EXISTS:A:projectID=FIELD:ID
EXISTS:A:status=AA AD
EXISTS:A:status_Mod=in

plannedCompletionDate=$$TODAYby
plannedCompletionDate_Mod=between
plannedCompletionDate_Range=$$TODAYey
portfolioID=6054ad91001a2963e92229aeb0af6de4
portfolioID_Mod=notin

[-Jaye]

Thanks Skye, that's a great call-out about using project users. I normally wouldn't touch that with a 10ft pole since we don't keep the names up to date, but in this scenario I think you're right and that it should work! I did modify the EXISTS statements slightly to look for tasks with Planned Completion dates of "this year", instead of the project. I should have updated this post with some findings - I don't remember all the scenarios now, but a few things were happening including the fact that some tasks' assignment statuses were showing as AA or AD because someone else other than the task assignment updated the task status to Complete (so, we DON'T want to exclude the projects where that's the case).

Here's what I ended up with. It's...interesting:

portfolioID=6054ad91001a2963e92229aeb0af6de4
portfolioID_Mod=notin
status=ONH
status_Mod=notin
DE:Project type=General operations
DE:Project type_Mod=notin

EXISTS:A:$$OBJCODE=ASSGN
EXISTS:A:projectID=FIELD:ID
EXISTS:A:assignedTo:roleID=605e087c00740f47158a61811b214ef2
EXISTS:A:assignedTo:roleID_Mod=in
EXISTS:A:task:plannedCompletionDate=$$TODAYby
EXISTS:A:task:plannedCompletionDate_Mod=between
EXISTS:A:task:plannedCompletionDate_Range=$$TODAYey
EXISTS:A:task:status=NAA
EXISTS:A:task:status_Mod=notin

AND:1:EXISTS:A:$$EXISTSMOD=NOTEXISTS
AND:1:EXISTS:A:$$OBJCODE=ASSGN
AND:1:EXISTS:A:projectID=FIELD:ID
AND:1:EXISTS:A:assignedTo:roleID=605e087c00740f47158a61811b214ef2
AND:1:EXISTS:A:assignedTo:roleID_Mod=in
AND:1:EXISTS:A:status=AA	AD
AND:1:EXISTS:A:status_Mod=in
AND:1:EXISTS:A:task:status=NEW	INP
AND:1:EXISTS:A:task:status_Mod=in

It seems to be working for now, but time will tell if any other unique situations pop up that need to be accounted for.

I'll mark your answer as the correct reply since I think it would have been perfect if didn't have to account for some oddities!