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!
Topics help categorize Community content and increase your ability to discover relevant content.
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.
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:
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:
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!
I've been reading over your Leap presentation slides in an attempt to try and create the following project filter:
I have this so far, but it error's out:
Anyone have an idea where I'm wrong?
This one can actually be done without an EXISTS filter. It still requires text mode, though. Here's what it would look like:
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.
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.
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.
‚‚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?
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.
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.