Help with my EXISTS/NOTEXISTS statement | Community
Skip to main content
-JC
-JC
Level 6
May 14, 2024
Solved

Help with my EXISTS/NOTEXISTS statement

  • May 14, 2024
  • 1 reply
  • 750 views

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!

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by skyehansen

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

1 reply

skyehansen
Community Advisor
skyehansenCommunity AdvisorAccepted solution
May 19, 2024

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

-JC
-JCAuthor
Level 6
May 22, 2024

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!

Terms & ConditionsAccessibility statement

Loading footer...