EXISTS filter on a project report

[NickVa1]

Hi WF Community,

My attempt at this request pretzeled my brain, so I'm reaching out to the big guns (here) to see if assistance can be provided?

I have a project report, whereby I want to apply an EXISTS filter that will look the tasks within a project, and if a task name contains "QA", "Proof", or "Quality Assurance" it pulls in that project to the report.

I know going up object levels is usually easier, but wondering if going down (Project down to tasks) will work?

Thanks much.

Nick

[AnthonyIm]

Hi - so below is the info from my "cheat sheet" when it comes to EXIST filters. (You'll want the moving down the Hierarchy)

However, I'm not sure you can do multiple Contains filters. I think I remember reading in a post on Community once, that it doesn't work. But try it. You will just repeat the EXIST filter but change the 1's to 2's (and then to 3's for the third one).

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

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

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

EXISITS:1:portfolioID=FIELD:ID

EXISTS:1:ownerID=532376910024d8443277c3e46ed5330a

[Jcosta16]

Hi Anthony!

This is such helpful syntax! I was wondering if you could see where I'm going wrong with this - on a project report, I'm trying to filter out any projects where the milestone name AUDITS RECEIVED doesn't exist. Is that possible? I'm wondering if the obj code needs to be MILESTONEPATH?

EXISTS:A:$$OBJCODE=TASK
EXISTS:A:milestoneID=AUDITS RECEIVED
EXISTS:A:milestoneID_Mod=notin
EXISTS:A:projectID=FIELD:ID

[NickVa1]

Thanks for this, Anthony! While I was waiting to see if anyone would answer this, it looks like I came up with something similar to what you have listed at the end of your response....

EXISTS:1:$$EXISTSMOD=CICONTAINS

EXISTS:1:$$OBJCODE=TASK

EXISTS:1:name=QA

EXISTS:1:projectID=FIELD:ID

Thanks for the info, and the note about adding 1, 2, 3 for multiple contains. I'll let you know if it works. So far, the above, single-contains, filter works.

[NickVa1]

Hmmm, @Kyna Baker - inactive‚ , do you think Tony Messam might know if I can do multiple contains in an EXISTS filter? I tried the following and it doesn't seem to be working:

EXISTS:1:$$EXISTSMOD=CICONTAINS

EXISTS:1:$$OBJCODE=TASK

EXISTS:1:name=QA

EXISTS:1:projectID=FIELD:ID

EXISTS:2:$$EXISTSMOD=CICONTAINS

EXISTS:2:$$OBJCODE=TASK

EXISTS:2:name=Quality Assurance

EXISTS:2:projectID=FIELD:ID

EXISTS:3:$$EXISTSMOD=CICONTAINS

EXISTS:3:$$OBJCODE=TASK

EXISTS:3:name=Proof

EXISTS:3:projectID=FIELD:ID

[kynabaker16]

That's a good question! @Anthony Messam‚ have you ever run across something like this and can share your EXISTS expertise??

[AnthonyMe1 · Accepted solution]

@Nick Valeriote‚

If I understand the need correctly, I think this can be done without EXISTS. Here's the filter I would use:

tasks:name=QA

tasks:name_Mod=cicontains

OR:1:tasks:name=Proof

OR:1:tasks:name_Mod=cicontains

OR:2:tasks:name=Quality Assurance

OR:2:tasks:name_Mod=cicontains

@Anthony Imgrund‚ 's cheat sheet is great. In the "Moving Down in the Hierarchy" section, I'd add that EXISTS is only needed if you're moving more than one level down or if you're trying to build an "exclusive" report rather than an "inclusive" one.

I would consider this report "inclusive" because you're trying to find all projects that do include tasks containing those words in the name.

An "exclusive" report would be one where you're trying to find any projects that have zero tasks with those words in the name. In other words, you want to exclude all projects that do have those tasks.

I hope this helps!

[NickVa1]

Yes! That's the money, Tony!! Thanks for explaining, too.

[angielawrence]

Please forgive me for resurrecting an old thread, but I am trying to find an answer (or a resource to teach me) and this had the best example to use.

If Nick HAD needed to use the EXISTS, what does the structure look like with the OR statement? I am trying to modify a filter on our Projects tab which currently uses exists to list Projects assigned with specific criteria to a user, but I'd like it to include (as an OR) as separate scenario where the next Task AFTER the assigned Task is still not complete (it's assigned to someone else). Trying to catch any that are missing the last step specifically in the user's Project list.

Is it EXISTS:OR:B:_________? (The first set is A). Not sure how this should properly be laid out.

Thank you!

[AnthonyIm]

Hi - If you want an OR with an EXISIT Filter, you add the OR:1: in front like you would with other filters.

For an example: Here is one for an assignment report. Show all assignments for people in the Creative Services Team or Creative Executive Team whether it is their home team or not. You will see the second team has the OR:1: in front of it.

EXISTS:1:$$OBJCODE=USER

EXISTS:1:ID=FIELD:assignedToID

EXISTS:1:teams:name=Creative Services Team

OR:1:EXISTS:1:$$OBJCODE=USER

OR:1:EXISTS:1:ID=FIELD:assignedToID

OR:1:EXISTS:1:teams:name=Creative Executive Team

[angielawrence]

Thank you!