Open projects with all tasks completed

[NickVa1]

Hi WF Community!

I'm trying to build a report that shows me all projects with an open status, but all tasks within these projects are marked complete.

I have a project report and have been working with EXISTS in text mode, but can't seem to get the results I need:

EXISTS:1:$$OBJCODE=TASK
EXISTS:1:status=CPL
EXISTS:1:status_Mod=in
portfolioID=6643ae07001383f67aba2bd89c1b13c5 6643ad3200130535e0b559b05c416dbc 6643ad7d0013369ab04ab960a389c3e5 6643add300136eed5ce84b52a5b277e9
portfolioID_Mod=in
status=APR CPL RAI ADH
status_Mod=notin

Anyone know where I might be going wrong?

Thanks,

Nick

[Doug_Den_Hoed__AtAppStore · Accepted solution]

Hi @NickVa1,

Perhaps percentComplete = 100 and Status not equal Complete would suffice.

Regards,

Doug

[JyotiNa]

Yes to Doug's point, I have "completion mode" set to "Manual" in my Workfront instance for most of the project templates and so i use a project report to filter off by "percent complete" = 100 and "status" not equal to complete to pull the list of projects awaiting to be completed as all tasks are complete. 

[skyehansen]

I think everyone else is giving you good advice so to answer generally (covering exists statements use) if you refer to the documentation here:

https://experienceleague.adobe.com/en/docs/workfront/using/reporting/reports/text-mode/create-comple...

You are first of all, missing the line for the linking object (shown in the article as "EXISTS:A:<Linking Field on the Linking Object>=FIELD:<Linking Field displayed on the Original Object>")

There's also some missing understanding here, in that if you were to set up a filter like this, it would pull in any project with at least one complete task. What you're actually looking for are projects with NO incomplete tasks, which I know, is semantics at its finest here. But you basically need to flip everything upside down and do a NOTEXISTS filter and look for tasks that are not complete.

[NickVa1]

Thanks everyone!

Yes, Doug's solution is actually simpler.

But good callout, Skye - using NOTEXISTS and yes, semantics in this case matters.