Project report showing projects NOT having a milestone task

[_Manish_Singh]

Hi Workfront Community,

I'm looking for a solution that might be a little bit complex but your suggestions to achieve this are welcome. To be honest we are struggling to get something which looks very simple.

So.. we want to track projects that do NOT have a milestone linked on any of its tasks list. Example: Let's say we have Initiation, Design, Delivery and Go-Live milestones on a milestone path. I'm trying to identify projects that do not have "Go-Live" linked on its tasks. We want to make adding "Go-Live" as a mandate for our PMs to produce KPIs.

I believe we'll have to use 'collections' here because referencing does not work down the object hierarchy.

Thanks!

[AnthonyIm · Accepted solution]

Hi - once you get the Milestone ID for Go-Live, you can use the below. Make sure you do all the regular project filters you want first. Then you switch to text mode and paste this underneath everything.

EXISTS:1:$$EXISTSMOD=NOTEXISTS

EXISTS:1:$$OBJCODE=TASK

EXISTS:1:milestoneID=c018bbf4237645cae053a7656a0a9384

EXISTS:1:projectID=FIELD:ID

*NOTE: Change the bold ID for your milestone ID

[RandyRoberts]

So, the project would have a milestone path but no tasks would be marked with the "Go-Live" milestone?

[AnthonyIm]

This would show you all projects (that fit your project filters) that do not have the Go-Live milestone whether the milestone path is attached or not.

If you only want ones with the milestone path attached, make sure that is one of your filters before you switch to text mode :)

[AnthonyIm]

Sorry Randy!! I just realized you weren't the original poster. HAHAHA! Friday Brain is starting a little early this week.

[_Manish_Singh]

Thanks guys for such a quick response. The text mode works perfectly.

‚

We should have just asked instead of struggling on our own, but it was a good learning experience 🙂

[_Manish_Singh]

@Anthony Imgrund‚ : This is not urgent. When you get some time, can you explain how to get last piece of code "EXISTS:1:projectID=FIELD:ID" ? (or) the logic behind it please?

I have another piece of code related to queue topics which looks kind of similar and I want to understand how to do it on my own next time. I'm checking API explorer but can't figure out.

This is on a queue topic report:

name=1 -

name_Mod=cicontains

EXISTS:1:$$OBJCODE=QUED

EXISTS:1:ID=FIELD:queueDefID

EXISTS:1:project:status=CPL,DED,REJ

EXISTS:1:project:status_Mod=notin

So basically trying to understand how to get "EXISTS:1:ID=FIELD:queueDefID" or "EXISTS:1:projectID=FIELD:ID".

Thanks!

[AnthonyIm]

No problem!

So in your example, you are "moving up the hierarchy" to QUED (aka Referencing). So on the OBJCODE QUED, you look at the ID field and that equals queueDefID on the current object

For the Milestone information, you are "moving down the hierarchy" to Tasks. So on the OBJCODE TASK, we need a field that is the same as on our current object (Projects). So that is why we have projectID first and say that equals the ID on the project.

Hope that makes sense. Below is the cheat sheet that I have in One Notes to help me out (It is from a LEAP session with the amazing Jason Webre and Tony Messum)

Moving Up The Hierarchy

This is for reports where you want to jump up more than one level. (Like show me projects where the portfolio owner is no longer active)

EXISTS:1:$$OBJCODE=[Insert the Object Code for Reference]

EXISTS:1:[Reference Object Type's field that matches current object]=FIELD:[Current Object Type's field that matches reference object]

EXISTS:1:[Field on Reference being filtered]=[Condition]

Based on my Example:

EXISTS:1:$$OBJCODE=PORT

EXISTS:1:ID=FIELD:portfolioID

EXISTS:1:owner:isActive=False

Moving Down The Hierarchy

This is for reports where you want to apply a filter based on a many-to-one relationship (Like portfolios that have projects Anthony owns)

EXISTS:1:$$OBJCODE=[Insert Object Code for the Collection]

EXISTS:1:[Collection Object Type's field that matches current object]=FIELD:[Current Object Type's field that matches collection object]

EXISTS:1:[Field on Collection being filtered]=[Condition]

Based on my Example:

EXISTS:1:$$OBJCODE=PROJ

EXISTS:1:portfolioID=FIELD:ID

EXISTS:1:ownerID=532376910024d8443277c3e46ed5330a

Showing Items Not Selected

This is for reports where you want to show items where a multiple-select option is NOT selected (Like projects that are not Digital)

EXISTS:1:$$OBJCODE=[Current Object Code]

EXISTS:1:ID=FIELD=ID

EXISTS:1:[Custom Field You Are Filtering On]=[What the TRUE expression would be]

EXISTS:1:$$EXISTSMOD=NOTEXISTS

Based on my Example:

EXISTS:1:$$OBJCODE=PROJ

EXISTS:1:ID=FIELD=ID

EXISTS:1:DE:Type of Project=Digital

EXISTS:1:$$EXISTSMOD=NOTEXISTS

[RandyRoberts]

I saw this in one of your older posts and made a PDF to reference. Excellent info!

[_Manish_Singh]

Thank you for taking time and explaining. Really appreciate it!

‚

Your explanation will answer many of our questions 🙂