Leap Session Q&A - EXISTS Filters

[AnthonyMe1]

For this year's leap, my session was about using EXISTS filters in reports. For those of you that have had the chance to watch, I hope it was informative. For anyone who hasn't had the chance to watch yet, you can find it at this link. If you have any questions about anything I shared in my presentation, let's discuss them here. I'm looking forward to hearing from you!

[skyehansen]

hey Tony, I feel like I still have a hard time determining when a problem's solution lies in using the Exists statement.

For example, if I want to show all objects (all projects, for example) that use a particular custom form -- or that do not use a custom form, is that an exists statement or is there a collection of custom forms that I must tap?

The struggle for me is that multiple custom forms can go on an object and I only ever seem to be able to filter on one. We have a workaround using custom form fields, but I really am curious on what the best answer is.

Thank you!

[AnthonyMe1]

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!

[NickVa1]

Hi Anthony.

I've been reading over your Leap presentation slides in an attempt to try and create the following project filter:

• Projects that have hour entries that have $0 in the hour's Actual Cost field

I have this so far, but it error's out:

EXISTS:1:$$OBJCODE=HOUR

EXISTS:1:ID=FIELD:projectID

EXISTS:1:actualCost=0

Anyone have an idea where I'm wrong?

Thanks.

Nick

[AnthonyMe1]

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

[NickVa1]

Thanks, Anthony.

Is there anything else I should add in text mode, along with what you've posted above? I only ask because it doesn't let me save the statement you posted above...almost as if it's incomplete.

[AnthonyMe1]

It works for me exactly as written. In the past, I've had experiences where existing reports are almost "corrupted" and adding text mode filters to them doesn't work.

I'd try creating a brand new report from scratch and paste this text in to see if that works.

[NickVa1]

You are correct. Thanks!

[NickVa1]

Hi Anthony. It's odd, when I run an Hours report, whereby I can select the Hour's Actual Cost field (=$0), the results show 104 projects.

When I run a filter on a Project report containing the text mode you provided, it shows me 1 project that contains hours with an Actual Cost field value of $0.

Do you know what I'm doing wrong? Both filters for each report allegedly have the same filtering.

[MichelleBo1]

‚‚I struggle with when to use the EXISTS filter. Is it just when jumping across and object where there is no direct link? I saw the 3 filter limitations in your slide deck but still struggle. Like on the 3rd limitation where data sets don't exist why doesn't not equal to x work?

[AnthonyMe1]

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.

[YvonneMi1]

I have more of a need for this as it relates to columns - could it be applied to columns or it only works for filters?

[AnthonyMe1]

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.

[YvonneMi1]

Thanks so much, I'll check that out. Great session!!!!