Leap Session Q&A - EXISTS Filters

Hi Skye,

‚

Good to hear from you.

‚

In the two scenarios you laid out, you're correct in mentioning that there is a custom form collection to tap into. However, only one of the two requires and EXISTS statement. In order to find all projects that use a certain custom form, you will have to use text mode to filter on the objectCategories collection. Here's what that would look like, just replace the sample category ID with your real one:

‚

objectCategories:categoryID=abc123

objectCategories:categoryID_Mod=in

‚

The other scenario you mentioned -- finding all projects that do not use a certain custom form -- does require EXISTS because we are trying to report on something that isn't there. So we'll use the NOTEXISTS modifier mentioned in the session. Here's how that one would look, again replacing the sample category ID with a real one:

EXISTS:A:$$OBJCODE=OBJCAT

EXISTS:A:$$EXISTSMOD=NOTEXISTS

EXISTS:A:objID=FIELD:ID

EXISTS:A:categoryID=abc123

‚

For me, the need to use EXISTS usually depends on if my filter need falls into one of the three different filter limitations that EXISTS filters can specifically address.

Hope that helps!

Nick,

This one can actually be done without an EXISTS filter. It still requires text mode, though. Here's what it would look like:

hours:actualCost=0

hours:actualCost_Mod=cieq

Michelle,

‚

To answer your first question, I use EXISTS only when faced with one of the three limitations mentioned in the presentation. I'll list the three limitations and try to help clarify each one. I find it a little difficult to put it into writing, so I am actually thankful that you're challenging me to do so.

‚

• Jumping multiple object tables
  • Without EXISTS, filters in reports can only make one jump away from the object your report is based on. For example, in a task report it is one jump to get to things like the project table, the assigned to (User) table, or the hours table. So I can filter off of any of those objects without having to use Exists. However, items like portfolio and project owner (User) are all more than one jump away from the task table. For both of those examples, I would have to first jump to the project table and then to the respective table for those items. Using EXISTS, I would join to the project table in both cases.
• Finding items where a multi-select field doesn't include a specific selection
  • The reason that this filter can't be built without EXISTS is that Workfront looks at selections made in a multi-select field individually, rather than collectively. As an example, imagine a checkbox field with three choices: Option A, Option B, and Option C. The logic you want Workfront to take is "Show me all objects where my multi-select field as a whole doesn't include Option A"; this is achievable only using EXISTS. Writing out such a filter using standard text mode instead applies the logic of "Show me all objects where at least one of the options selected is not Option A". The EXISTS filter will successfully only give you projects where that field is blank, only B is Selected, only C is selected, or B & C are selected. None of the projects returned will have option A. The non-Exists attempt at this filter will only exclude projects where option A is the only selection. So your report will include projects with A & B, A &C, or all three selections. The NOTEXISTS line available in an EXISTS filter functions differently than the standard "not equal to x" that you can create in standard text mode.
• Empty Data sets (reporting on things that don't exist)
  • There isn't a whole lot I can do to clarify this one, so my apologies if my attempt at doing so isn't helpful. The main challenge with this filter is that you're trying to report on something that doesn't technically exist. All of the standard filters that you can create using either the builder or normal text mode will search actual objects and then choose which ones to include. With EXISTS, you can build a filter out and then tell Workfront to exclude everything that matches that using the NOTEXISTS line. The NOTEXISTS line works uniquely and there is no equivalent to it within the report builder or standard text mode. Using the builder or standard text mode there is no way to find Portfolios with no Projects, Users with no Hour Entries, etc.

I appreciate you asking this question. Because of your question, I've put some more thought into how I articulate this and I hope I've improved on what is included in the slides and the recording. I'll definitely be incorporating some of this into next year's presentation.

This session specifically applies to filters. However, there was a separate session presented by my colleague Jason Webre that covers a concept known as Collections. His session focuses on columns in a report. The title of his session is Collections: A Whole New World of Advanced Reporting and it can be found on the business track on the Leap site.