Your dream Workfront report EXISTS! Two Approaches to Mastering EXISTS Statements

Transcript

So welcome, your dream report exists, two approaches to mastering exist statements. So before we get started, I just have a little housekeeping because I know you don’t want to hear from me and that’s, you’re absolutely right. This session is being recorded and I know we’ll probably have people that come in throughout the session and we’ll repeat that, but the session is being recorded and you are gonna want to rewatch it. You’ll get the slides, the resources that we talk about, follow up, all of that will happen today. I do want to just say one thing about the followup. I know everyone’s gonna have a lot of questions and if we can’t get to all the questions, I’m gonna pull them from the chat and then we’re just gonna try to manage them as best we can, but I just don’t want you to think that you’re getting ignored or anything else. I just like, there might be a lot of questions in the chat and there’s only Sky and Nathan. So we’re gonna go ahead and move to the agenda. So we’re doing our introductions, we’re gonna have them do their presentation and then we’ll do a wrap up. I do want to make another little note. Now this is on the more advanced side of Workfront and that’s okay. So if we have new Workfronters out there and you signed up and you think, oh, wow, this seems more advanced. What I want you to do is not give up because this right here, this session is gonna be that session that opens your mind to like, oh my gosh, this is what’s possible in terms of reporting what I can do. So, and if you are a new Workfront or we have literally all been in your shoes, so just hang in there and if you’re advanced, you know this is gonna be great advice. Okay, with that, your scale team is here, you’re not here from us, but all three of us are here. We’re former customers, former support, we’ve been Workfronters for decade each, I think about, so we’re here if you need us.

Now, let’s get to it. Our speakers today, Sky Hansen and Nathan Johnson are gonna talk to us, so I’m gonna stop sharing and they’re going to go ahead and jump in. Let me stop sharing my screen. Okay.

How about that? Yeah, let me, oh, what am I doing? I mean, I love the airport, it’s fabulous.

There we go, you guys can see that? Perfect, yes sir. Okay. Love it.

And it’s loading, it’s loading, all done. Yeah, don’t know why that’s taking a moment.

It’s okay. Yeah. I saw it and then it went away. Yeah, one moment there with that.

Okay.

Now it’s showing, right? Fabulous, yes sir. All right.

All right, so thanks for the introduction, Cynthia.

Yeah, Sky and I partnered up and let us introduce ourselves and our origin stories.

Go ahead, Sky. Oh yeah, yeah, yeah, yeah. So origin stories, how I got into Exists. I started working for a new company in 2018.

The departing system admin on his way out said, hey, I just got permission to delete 10,000 projects. And I’m like, sweet. We just got permission to delete 10,000 objects. So I deleted all the projects, took me all day. A couple of months later, I went, why do I have these empty portfolios and programs? And why have all these templates never been used? Anyway, the consequences of not thinking things all the way through. If I had it to do over again, I would have probably done the same thing. I mean, those projects needed to be deleted. They weren’t being used anymore, but I probably should have thought through every single thing like, okay, as projects are being deleted, means portfolios are being emptied out. It means templates will look as though they’ve never been used. People who haven’t created projects in the past year will suddenly show up so I can repurpose licenses, like all of the things. Anyway, I was complaining to Jason Weber one day and he said, you know, I have just so much work ahead of me. I’m gonna have to come through the portfolios one by one to see what they have. And he said, no, no, no, let me, here. Here’s an exist statement that you can use to find empty portfolios. And I went away and it worked and it was like black magic. I came back to him next week and I said, and I also need the same thing for templates, templates that aren’t being used. And he gave me a second one and I went away and I came back. Anyway, 2019, he came out with the first exist class. And I’d like to say I never went back to him again, but I went back to him a couple more times.

That’s awesome.

Just so you guys know, from a support perspective, Sky, the reason why we know each other is because she’s kind of famous here. When she reaches out to us, it’s because something is actually broken. Instead of, you know, a lot of people will be like, hey, my report’s not working. And we jump in and it’s like, oh, your filter’s wrong or something like that. But with Sky, we know that there’s something actually broken.

She’s gonna downplay herself a little bit in this presentation, but she knows her stuff.

For my origin story, I joined support.

I came with a background with SQL. And so I went right into the reporting pod on one of the first customer calls that I was a part of. They were trying to create a proof approval report filtered by project status.

And I initially tried to do all the stuff that we’re gonna show you what you would normally try to do first, right? You try to build out in text mode, you try and jump tables to create that filter, but then you run into problems. So it took 15, 20 minutes live for me to figure out my first exists. And it was pretty rough. It was a scary moment there. I don’t wanna tell you how long it took me to build my first exists. I can’t laugh, it took me like hours.

It sometimes takes me a long time too. Like that was an easier example, but there’s a, yeah, there is not a time limit when it comes to exists because they will save you time no matter how long it takes you to build them. So what are we gonna do? We’re gonna help you guys figure out why exists work. We’re gonna teach you what are exists, why you need to use them.

We’re gonna go through my approach with a little more technical, taking that SQL background and then Skye’s approach with a little more business driven. And then we’re gonna put it all together and show you a few finished examples of existence.

All right, so what is exists? Here’s some context for you because I know we have some beginner reporters in the audience.

I wanna break it down in terms of beginner intermediate advanced, beginner basic level filters. This is where we all came in at. And how I want beginner reporters to take a look at their filters is these filters are all one-to-one. So each item in your report has one yes answer to the filter. So if it’s a project report looking for projects created last week, your report starts going through all the projects in the system says, was this one created last week? No. Was this one created last week? Yes. Put it in the report. Was this one created last week? So everything is yes or no. All the yeses make it onto your report. All the nos just don’t.

85% of it standard mode. So you can literally go into the filter area and then just pick off the list. 15% of it is easy text mode is what I say. So you flip it to text mode and then you do one additional thing.

For intermediate collections, now we’re talking one to many. So each item in your report has more than one possible answer. So here’s where, for instance, if you’re looking at a program report and you’re looking for programs where projects in there were created last week. If a program had 10 projects and one of them was created last week, that program will make it into your program report, right? Yeah. Yeah. So the only programs that are not going to make it into that report are the ones where they’ve gone through all 10 projects and gone, yep, none of these were created last week. Okay, toss that one out the door. Awesome.

95% Just to jump in. Collections, a lot of people consider that kind of advanced. They’re not too hard once you get the hang of them, but they are a step down from Xist. So yeah. Yeah, they can be a little tough. And most of them are text mode, which is why people go, oh, these are tough. They’re text mode.

And I think Workfront, I feel like Workfront makes it a little tougher on people to understand because Workfront does bring in a few cheat fields, like the assignment users field and task reports. That’s collections, right? But Workfront allows you to use it, even though you’re a beginner, like you can literally pick it off the list. So that makes it hard for people to understand, and it’s a little hard for people to understand. Awesome. Yeah. And then we go to the advanced granddaddy filter, Xists. So this is a sample of an Xist statement. You don’t have to memorize it, not going to be a test.

Xist filters are advanced. They’re all text mode. They’re 100% text mode. And this is the granddaddy, many to many items in one group being checked against items in another group. And a match only occurs when at least one pair meets that condition.

So that’s it, one to one, one to many, many to many.

Cool. And we’re gonna break down each line for you in just a bit. And it’ll be a lot less mystical than it looks right now.

So what can Xist do for you? So I just wanted to show a couple of cool examples. These are things that maybe we tried to word it like your boss would tell you to do this. And then this is where you would approach it. So again, we’re not teaching you yet. This is just an example. So maybe your boss comes to you and says, can you get me a list of all templates that have not been used? Users have too many choices, so we wanna retire some.

Pretty simple request.

Because we’re saying we want a list of templates, we’re gonna start with a template report, if you see that in that screenshot. And then we’re gonna go into filters. We’re gonna change it to text mode. And we’re gonna put in the Xist filter in text mode. Now this one has a line that says not Xist. All the examples we came up with happened to be these not Xist ones. We’re gonna explain that at the end. We’re gonna teach you how to do just Xist first, and then we’ll add that not Xist. But this is gonna show you now all templates that have never been attached to a project. On the right side, we’re spending too much on licenses. Can you determine who isn’t using theirs? So again, we wanna see user list, so we’re on a user report. But then the Xist statement, if you look at it, you might be able to see a few things. So we’re using object code hour, entry date, not null.

So this one right away is show me users who haven’t, either who isn’t using, who haven’t submitted an hour.

They haven’t put any hours into the system on anything. So they haven’t been showing their work. So we just wanna see all those users.

Okay, so let’s jump in to the first exercise. We’re gonna give you a word problem, and we’re gonna actually show you how we approach building an Xist statement. Yep, so this is a question I would have gotten five or six years ago. Show me all the tasks that are in programs that are owned by the logged in user. And if you flip to the next slide, this is how I would have tried to solve it. First attempt, create a normal filter. Go into your filters area, select a field, type in program owner ID, and then when Workfront tells you, hey, that doesn’t match anything, you go, oh my God, Workfront, thank you for nothing.

So then we move to our second attempt, which should be to create a normal text mode filter. I’m gonna give you handy hint number one, which is avoid creating any text mode from scratch. Don’t ever go in there, bring up a blank screen, and then start typing.

I always, always look for something similar in the standard mode, and then flip it to text mode and start monkeying away with it. So if you’re saying, hey, I’m looking for the tasks, programs, owner ID, I’m thinking to myself, okay, I think I know how to flip something to text mode to get that, but first I’m going to put it in my report view, because if I do it there and it shows with results, then I’ll definitely know I’m onto something. So I go over to my view and my task report. I start typing in program to see what I can get. It says, well, you can have the project program ID. We’ll give you at least that much. And I go, all right, yeah, project program ID. I bet I could flip that and just put in owner somewhere and let’s see what we get. So when you flip to the next slide, you’ll see that I did at least that. So I’ve got my column, I flipped it to text mode, I put in owner ID, and when I ran the report, I actually got a program owner ID. So now I know this is probably what my field is supposed to look like. Supposed to be project colon, program colon, owner ID.

This is a task report.

This is task, you’re going to project program owner ID.

So I’m cooking with gas. I got something that works in my view. So I’m just gonna take it over into my filter and see what I can get. And of course, when I, I mean, give me an emoji if this has ever happened to you. You take something that’s perfectly working in your view, you stick it in your filter and Workfront gives you the little red triangle of death and the little word pop-up that says you failed, Sky. Like I’m pretty sure if you zoom in on that, you can see my name on it, Sky you failed.

Yeah, and this isn’t very nice. Yeah, Workfront doesn’t always have the best error handling. So I’m sorry about that experience.

You want me to put in a ticket for that? Actually, this is just user error, sorry, Sky.

So what the heck happened? Let me explain.

It’s too far away. So why columns can see what filters can’t. So this is just high level information for you. But if you can see something in a column by doing table jumps, that doesn’t necessarily mean you can then use that same table jump text mode in the filter. So our engineers design columns to allow up to six table jumps. That way you can just display things that are further away. So on a task report, you can jump through project program. You can even go up to portfolio and you can show information on the portfolio table. However, in filters, we only really allow one table jump. So you can go up to project and show information on the project table and then filter on that information. But you can’t take it another step further, unfortunately.

So what is our solution? Let me explain how exists.

Use the relations between different objects to help you bridge that gap. So this is a relational database diagram. Some of you may know what this is from like a SQL class or something like that. But this is how you would design the logic of a database. And everything in a database, all the different objects are going to be related to each other through different keys. So they’re linked to each other using a unique identifier or key. A primary key is the unique identifier. So that’s the ID or the GUID, right? The GUID of different objects.

In this case, it’s kind of like a phone number, right? If these are different people, we’re saying like, everyone has a unique phone number. That’s how I can contact them. That’s the primary key. And foreign key, we sometimes put on an object the primary key of another object so that you can create a link. That’s a foreign key. That’s my cell phone. That’s my cell phone. It’s carrying everybody’s number. Right. So, yeah. Yeah, and I have your number. So if I’m the task table. I know my phone number and then I have FK of everybody else who’s in my cell phone. So if I need to contact them, if I need to contact my friend project, I just dial up their FK and I say, hey, project, what’s your description? And then the project tells me and I don’t have to carry the description around, right? Yep. Yeah. And some objects don’t have information about other objects though. So task does not have the phone number of program. So we can’t call program up directly. We have to call project first and say, hey, project, can you give me programs number so that I can then contact program and get their information.

I don’t know why I’m asking since you’re muted, but that game, you know, everyone in the world is six degrees away from Kevin Bacon. They know somebody who knows somebody and there’s no more than six degrees. So in the same way, task and portfolio, for example, task doesn’t have portfolio’s number, but if task can call up project and project can then call up program, program has portfolio’s number, then we can link them all together and task can then talk to portfolio. So we can create that connection by using the primary keys of other people. So their phone numbers.

Now, one thing before I move on that one to many. So if you see that at the bottom there, now a single project can have many or multiple tasks, but each of those tasks only links back to one project. So that’s that one to many relationship. That’s important for the collection text mode that we talked about earlier. And it’s also important when we get into how to build exist filters. So how do we see this? So I have this relational database diagram, but how do we see this in our API Explorer? So this is where you’re actually gonna be looking to see these relationships. So in the API Explorer, in the one to many relationship, the one, so task to project, right? There’s only one project for each task. That’s a reference. So the task has the foreign key or the primary key of the project. And so if I go to a task in the API Explorer and I go to the reference table, I then can find project, click on it, takes me to the project table.

Okay. If I’m on the project, I would have to go to the collections tab to find tasks, click on that and takes me to the task table. So it’s really important that we understand how all these different objects relate to each other and that we can draw a line between where we are and where we wanna go.

And I think we’re pretty lucky that this does tend to be fairly intuitive. I mean, I’m literally picturing a project being like a folder in a filing cabinet that’s filled with pieces of paper and these pieces of paper are all tasks. Right. So it’s not something that takes a lot of imagination. I mean, obviously projects have tasks and documents and issues. And as soon as I add that S to the end of everything, I’m kind of like, okay, project is a collection of all these different things. Right. Yeah, yeah. When we say tasks, that’s the collection within the project. And then we kind of know the relationship between them. It does get more complicated than what I’ve shown you so far. And we’ll get into that a little later, but yeah, it really is mostly intuitive, right? And we oftentimes talk about it as like parent to child relationship because it just kind of keeps going up. You know, you have a task and a parent task and a project and program.

So when we start using the Exist filter to then get around that one table jump rule. So that’s really why we’re using the Exist filter because we can’t go too far away.

We need to find a linking object. So there’s three lines in Exist filter. I’m gonna talk about the first two right now. The very first line is just, what is that linking object? I need to find an object that if I was on that object, I could build out a filter that doesn’t violate the one table jump rule. So from task to program, I can do that from a project. And there’s a link all the way between task, project and program. So if I was able to utilize projects information, I can then filter on program information.

So in this case, it’s kind of easy to intuit which one is the linking object, right? It’s right in the middle between task, project and program.

Another way to think about it would be, okay, from what object would I be able to do this one table jump rule? Sometimes you just go into the UI and build a filter that works on an object close by to the one that you’re trying to build. And if you could do that in the UI, then now you know where to link to, right? I need to link to project.

Then this line here, this is the linking line. This is how we actually build a link to project. So I’m connecting task to project and everything on the right side of the equal sign is where I am right now. So I’m on a task. So field stands in for task and I’m gonna use my primary key. And then I’m going to link to it from the project side. So the left side is going to be the linking object, building it so that tasks ID is equal to task ID. So these should be pretty much the same. This is a little bit hard to wrap your mind around, the field line, that is just where I am right now. If I’m building this so they can see tasks, I’m gonna start from task.

Understanding the relationships and how to draw that line, that’s how you build this part out.

Once I’ve figured out where I wanna go and I’ve built something that seems to work here, that seems to link them, then I just build the rest of the filter, which is actually just a regular filter. If I went to the project object and I built a filter saying, show me projects that are on programs where I’m the owner, this would be what I get if I switched to text mode.

So I can just grab that because I’m now on projects and just add it in here. So this is our filter. So really exist filters, little bit easier once you really understand them because you are just building a filter with one line and then the other two lines are telling us how do we build that filter from where we are now, we need a link to that object where we can build that filter.

Okay, hopefully I haven’t lost you yet, but that’s how I would approach, I try to understand the relationships and then I build out my exist statement.

Yep, and for me, it’s gotten to the point where it’s really the first two lines that give me a little bit of heartburn still like trying to figure it out. Once I figured it out, the third line for me is always easy or the filter is always easy because I have this easy text mode filter hack, which is I take a look at the object code, that’s the table I wish I was in. So in other words, that’s the report I wish I was building in. And then I just set aside my task report for now, open up a new tab in my web browser, separately create this project report, build my project filter, it’s usually in standard mode, switch it over to text mode and then copy and paste that text mode filter into my exist statement. So everything underneath the first two lines, I never think about anymore, I just kind of like find a report, build it in there and then flip it over. So for those of you who are visual, we’re gonna go to the next slide. It looks a little bit messy.

I love it. Yeah, I pulled it up and I said, Nathan, this looks a little messy, why don’t you do some magic and make it look better? And he said, well, I mean, you know, like it’s speaking the truth. So here I am creating that project report.

I did it in text mode, program owner ID equals the user ID, switched over to text mode and then copy and pasted that text mode straight into my exist filter back at task report. You can see that I didn’t copy the set. I never copy the inlines or equal lines because for some reason the exist statement never seems to need them. So keep everything nice and streamlined. It’s just one line for me.

But if it’s something like less than or one of those kinds of filters, you would need that, right? Yep. Yeah. So that’s, if we flip over to the next slide, I’ll show you version two, which is where the user comes back to me and says, okay, actually, yes, tasks in projects where I am the program owner, but I’m actually only interested in the program if it got created this year. So I’m not, I don’t have any heartburn over this. I go back to my project filter. I put in that and the program was created this year and then flip it back to text mode and then copy paste and the entire thing goes into my exist statement again.

And then if you flip over to the next slide, version three, if they come back to you one more time I know I said and, but I actually meant or. So even if I’m not the program owner, if any program was created this year, I wanna see it. I literally, I wanna see how I’m doing versus everybody else. So there I am back in my project report, I’m creating the filter as I would like to see it. So program owner ID is me or program entry date is this year. And then when I take it back into my exists report, this is what happens. The exists, exists colon one colon goes inside of my second filter. So it starts off or one and then exists one rather than the other way around. Cause I know people get a little bit confused about the syntaxing around exist statements. Yeah. You always put that or before they exist. And in this case, you’re repeating the linking object and the linking line because it’s now on two sides of the or.

So just repeating it. Awesome. Cool.

So exists, it’s the answer to everything, right? But why is it so hard? So these kind of are the hardest filters you can build. And there’s a few reasons for that. One of them is you just forget that they even exist or you don’t realize that you need to use them. You think, if I know the basic text mode and I can bridge that gap the way that Sky was trying to do earlier, you think it could do everything you need to or it should be in the UI, right? Why do we need text mode? And a short answer to that is just the developers have built our reporting tool in a way so that it doesn’t blow up on you, right? So you don’t overload it or cause any big issues. So sometimes we just have to go to text mode or advanced, but not knowing when to use it. That’s a really common one. I have people sometimes reach out to me and they’re like, I’ve been trying to build an exist statement for like an hour. And I’m like, oh, you could have just done that in an assignments report or you could have, that’s actually in the UI, you just didn’t find the field. And it’s kind of hard to know when, right? And after that experience, they might not want to try building an exist statement again, because it can be kind of tough.

That linking line feels a little bit like magic, right? It’s kind of hard to know how to build those relationships between two things. And how do I know which object code to use, right? So in the next one, we’re gonna have a little bit more difficult, not difficult, shouldn’t say that.

Little more convoluted, exist statement so that it’s a little bit harder to figure out which object code to use, but that is gonna be fun.

So why exists? So I’ll explain the first one and then we’ll go, and then Scott will explain a few and then we’ll finish this up. But this is a really cool diagram. You guys are gonna probably refer to this a lot. We tried to put like all the different reasons why you would need an exist statement into four categories. So one of them is many to many.

We’re gonna show you that in just a moment. But many to many is those relationships where you have, like a project can be shared to many users and users can be shared to many projects. So how does that look in a relational diagram and how does that look in the API Explorer and how do we find that relationship between them? Oftentimes, the API Explorer or a database will have an object that is a linking object between the two. So that’s really nice. But that’s kind of tricky.

Yeah, and I’m looking at these categories and I’m thinking to myself, many to many kind of like, these are all the different flavors of many to many in some sense.

But the first category is definitely the classic many to many.

And whenever I see this one, I’m always kind of like my mind blanks out. I’m like, Nathan, you take this first one. I’m never gonna say it well. But I can at least do the next two, which is the next category, at least one or just one is something that I get all the time. Like so for instance, hey, show me all the users that have created at least one project in the last 90 days so I can take away everyone’s license. I don’t really care to see the list of projects that everyone has created. I just want the names. And so while it’s, as a beginner reporter, I can at least do like a list of projects and group them up by name and have that summary. It’s a nicer, tighter report to just give people a list of users and say these ones have created or these ones haven’t created. Let’s deactivate them all.

And then of course, we covered check for something outside your immediate family as our first example.

I wanted Kevin Bacon’s number, couldn’t get it from my task report. So had to ask my friend Nathan who knows Kevin personally.

Yeah, and I was able to supply you his number. Yes, yes, which I will keep close to my heart.

And then there’s also find something that isn’t there or doesn’t exist. This is really, it could be any of the three before us, but I think it’s a good way to think of it as the at least one or just one and think of the opposite of that. So everything but one, right? Or so products that have no completed tasks. So you’re looking for a missing item and we’ll go into that a little bit. It’s kind of hard to wrap your head around that, but it’s actually easy once you build an exist statement and then you just add the not exist to it and then it does the opposite.

Okay, let’s take a breath before we do the second exercise.

All right, I’m done.

Okay. Oh no, I’m not done. We’re halfway through. We’re halfway through guys. Let’s level up.

We’ll go through this one a little bit faster because we don’t have to introduce all the same concepts, but this one’s a little hard. So we have a word problem. Show me all projects that are shared with a specific user.

Okay. So it’s common, right? You’re trying to find out what was shared and to whom. So like what projects are shared and to whom. And we can go into a little more detail showing, you know, what kind of sharing we gave. So is it just a manage or is it view? I’m gonna stop sharing my screen for a second here.

And I’m going to, or, sorry.

Here’s what I’m gonna do. I’m gonna show you guys what I would do to approach this.

So I’m in a project report, right? This is project shared with a specific user. So first step, let’s try to add a column. Let’s see if we can find shared with. Easy peasy.

What does that even show though? Right, let’s just run this so we get a feeling for it.

We’re gonna be a column here.

And so this is one of those nice ones. This is a collection actually. So it’s showing all the people that are shared to this report or this project.

And usually we would need text mode to do a collection. So that’s already telling me something’s a little weird about that. Now I wanna try to find that filter, right? Shared with.

I can’t find it.

So I know like the developers gave me this really cool field that shows a collection of stuff and I can’t even filter on it. So maybe, let’s see if it’s maybe a different object. Sharing settings.

I don’t, that doesn’t seem right.

Access.

So there’s access requested, calculate access requested, access level.

These aren’t looking right to me.

So Skye, what should I do? I’ve already given up at this point.

I went out for coffee.

I wish I could.

But I have a customer who’s relying on me right now. So I figured this out. So let’s see, project. Let’s figure it out.

If I’m on a project. Your immediate next step is, hey, let’s go over to the API Explorer and start poking around. Yeah, because I wanna find out, like I don’t have the time to just build out this relational diagram. And I don’t even know what objects there are, right? Because I haven’t looked at the API Explorer yet. And I’m trying to figure out what are the references. So this would be just a direct link from project.

And I like to do search and just see. Nothing shared, nothing access.

And that kinda makes sense because we’re sharing this project with multiple people, right? Or multiple teams, people, like multiple objects.

So users, teams, groups.

And so I’m thinking it’s probably a collection.

I don’t know what it’s gonna be called. So this is really hard when you have stuff like this where you’re not even sure where to go. You just need to understand though that there’s that relationship. It’s one to many because it can be shared with many things.

And then when we look at the collections, we gotta see if anything makes sense. Access rules is right at the top. So that’s kinda nice. I’m gonna look at that. Still not sure what that means. Sorry to interrupt you. Are you pretty much saying that most of you guys who build reports over there and support, you’re just really good guessers. You’ll just go into the API Explorer, scroll up and down, go, oh yeah, access rules. That looks like it. Yeah, sometimes.

Until you know it, you don’t know it, right? And so you just kinda have to look for it.

Now I could search. There’s the community. There’s, we have internal Slack channels where people ask these kinds of questions. So I could search for like, anybody know how to create a report based on who is shared with this object? And that might help, but oftentimes, yeah, you just kinda have to look through and you have to understand how things relate so that you can find it more easily. In this case, it doesn’t take many clicks. If I click on here, you see how this table is actually called share? That it, A-C-S-R-U-L, so it’s access rule, but the name of it is share. So that gives me, like, that makes me feel good. I have a feeling that I’m on the right track right now.

And then, but looking at it, I still can’t make much sense. So these are the fields. These are the, we have foreign keys. So anything with ID would be a foreign key to another table. And those would probably show up in my references, right? So I can go there. But I don’t know what accessor ID. Like, that sounds like whoever’s accessing it, so that might help. But ancestor, I don’t know. Core action, if I click into it, I’m now seeing some stuff here. So I have add, edit, limited edit, view, delete. That’s good information.

Security object ID, I don’t know what that is.

So how do I determine what all these mean? Skye has the answer for that. So I’m at this point where I have a good idea.

Let me share this again. And let me take it to the next page real quick. Just before you share how you figure out what these IDs are and what the values could be that we could use.

So this is a perfect example of many-to-many.

And so we have project, could be shared to many users, but we have to build. So since databases can’t represent this directly, there’s a linking object here. So we have this rule object. And there’s a lot of those. So there’s actually like assignments is a linking object between two things. Instead of just saying, this is assigned to this user and just having a direct link, we have an assignments object. We have object category objects, user roles. There’s a bunch of those actually in our API Explorer. And those will always be our linking object because that’s how we get between those two things. So I actually find these to be kind of easy once you understand it because we already have that linking object pre-built for you.

Yeah, so that’s kind of nice.

Yeah, and I’m the exact opposite to you, Nathan. I’m looking at this going too many lines that I don’t know the names of any of these things. Yeah. So yes, so here’s what we’re gonna do. I get it. You guys, we’re just gonna brute force our way through this. Okay. So yeah, handy hint number two.

This reference collections in a view piece of template blob that I’ve put in here for you guys.

I’m not Nathan. I’m not gonna go through line by line and kind of go, oh yeah, accessor ID. That looks like the most obvious. Yeah, this is the answer to all my questions. I’m kind of like, no, you know what? I’m just gonna add all of the likely suspects into one column, which is my view column. And then I’m going to look through and see which ones I can add to my linking lines, my filter lines, all the lines. So if you just scroll to the next slide. Resourceful. Yeah, so this is that on the right hand side, this is that template text mode that I spat out. And you can see all the things I use. I use the access rules that came from the API Explorer.

And then I use the simple value expression. It’s just the concat one. And I concat all of the things. I’m just guessing at this point, I go through and I say, maybe it’s this, maybe it’s this, maybe I want that. And I string them all in one long concat value expression. And then when I spit it out in the table below that, I just look and see, oh, accessor ID. That’s the person I’m looking for. And oh, security object ID. That’s the project I’m on. So I’m not doing any guessing here. I’m just kind of like, what are all these things? What information are they giving me? Yeah, and you can set that up too, where you can create a project that is shared with one person. So this is like, you get perfect results and you know what to look for. So this is definitely the way to go.

Though my way is pretty good too.

It is, it is if you’re a good gister. So putting our two ways together, here’s the solution. You asked to show all the projects that are, let’s say incorrectly shared with a specific user. So here we have Nathan. He’s only being shared with view permissions to a series of projects. Here’s how you can find all of them. You’ve got your core action equals view. You’ve got Nathan’s GUID or his phone number.

And then away we go, hit submit or save. And then the report will spit out everything you need.

I like how you came up with this example of core action equals view, because we want to see, Nathan is not supposed to have view access to things. He’s supposed to have more than that. He’s supposed to have manage or edit. So in this case, we’re going to see all projects where he only has view.

And then we can remedy all of those really quickly. So this is a really good real world example.

Hey, so how do we then switch that to not exist? So we want things without that match, right? So instead of everything, so in this case, let’s just say instead of looking specifically at view, right, because that was helpful that in a way that kind of helped us rule things out.

Nathan should be shared to everything. Let’s just find some stuff that he isn’t shared to because he is a super user and he’s on an admin. So he’s not like automatically shared everything. He’s a super user who should be shared to everything manually but maybe we’ve missed a few. So we could run this one right away.

Now we need to run this separately in every object that like this could be run in a project report. This could be run in a task report and it could be run in a program report because that same relationship between project and access rules is there between program and access rules. So this would work and you put my ID in and then we’d see anything that I’m not shared with and we can remedy that right away. And it’s not just for people. Accessor IDs are good for teams, roles and groups. So if you had to have a particular team shared and they weren’t, this is how you would find the team. Exactly the same thing. Team do it goes in accessor ID spot. It’s just very universal, very, very.

Yeah.

And then let’s go back to that original one that we did where it was like show me tasks and programs that I own. Let’s turn it around and say tasks and programs that I do not own. And so we add that not exist. And now we have, and this would be similar, right? But let’s go ahead and see if there’s any tasks out there that aren’t under a program that I own.

Cool.

All right.

We are almost to the end of the talk. I gotta do the next three slides in about three minutes. Okay, let’s do it. Okay, all right. So why exists? Here are your clues. This is the table I gave you at the beginning, halfway through the session.

Have you noticed that when you ask yourself these questions, these are clues. These are clues to what your object code is. They’re clues to what your linking line is. Does that make sense now? Yeah. Yeah, and you know what report we’re on, object code link. Yeah, so this is a good one to review.

Let’s get into our tips. Oh yeah, handy hints. All right, we just spent 10 minutes hashing through access rules. We didn’t have to do that. A-C-S-R-U-L is a word that doesn’t show up in the English language. If you went into the community and did a search on it, I bet you would find a bunch of oddly relevant posts to this. I could have saved myself 10 minutes right there. We could have shut the session down early.

Yeah, and what does ARVSTS mean? That is for the approval, step approvers. So it’s when you do a task approval or a project approval or that kind of thing and you want to figure out who approved what.

ARVSTS, I think there’s only one post on it and it just keeps getting referenced over and over again.

And once someone builds and exists using a certain object, all you really have to do is switch out the filter line. So someone’s built from this report to that object, they’ve already built the linking line and the link. Let’s just switch out the filter each time. Yeah, you never have to rebuild that again. Yep, so yes. Step number one, always search community first. Save yourself a little bit of time with the linking line, especially since it’s heartburn for a lot of people.

Step number two, I made myself a little template exist statement. Obviously you can see I make myself a lot of templates and every time I have to do anything, I just copy paste the template in. I use tasks and projects because I know that one by heart. On the task, the project ID is called project ID and on the project it’s called ID.

So yeah.

And then the other thing I did was I started my own reference library. So I keep samples of all my stuff. So I can mix and match. And I always try and include a description because sometimes I’m looking at a blob of code and going, what the heck is this even for? So don’t assume it’s gonna be obvious because you spent two hours on it. It’s not gonna be obvious. Just add the description.

Yep. And then to help with that, you have macro tools so you can easily fill those in. Yep, I never look for copy and paste anymore. I just do like a couple of quick keystrokes like colon F-E-X and it pulls up my filter for exists statements.

Yeah, so keyboard maestro, espanzo, auto hotkey and then cloud tools. Guys, we gave you homework. So we haven’t asked me anything. It’s coming in December. That’s five weeks away, a scant five weeks away. You could be week after week working on your exists homework. And then you could come in to ask me anything, ask questions, share your results, all the things.

Yeah, we’ve got them starting from beginner to more exciting.

You’ll see week four, we cover Kevin Bacon again.

So there’s a lot in here for you guys.

Yeah, yeah. And I’m gonna help do a QA for that and then it’ll be you and who else is doing it with you? There’s two of you doing it. Yeah, Kelly Gardner at this time. Okay, so you two are doing the AMA and then I’ll do some key review helping out. Yeah. Cool, all right. Well, that’s everything.

I’m gonna go ahead and stop sharing my screen. Yeah, I’m gonna just pull up our last slides, but just like, so we’ve got the AMA link. You’ll get it obviously in the follow-up as well but I put it in the chat as well. Make sure you get that on the calendar and then follow it. To me, their plan looks exactly like an exercise plan. Week one, you’re gonna start here in week two. So make sure you do that.

And we’re gonna keep these conversations going obviously but I can’t stress enough. I know that everyone said it in the chat. Sky just said it. Go out to the community. Do not be afraid to be like, hey, I’m trying to build this report and do this. It’s not working.

Our Adobe Workfront champion, Sky, and our community advisors will jump right in there and help you. So just wanted to do another little shout out or gotta go out to the community.

All right, I’m gonna do this real fast, I promise. Okay, so let’s go to wrapping up really quick.

And I’m going to put it in presentation mode. Yay, look at us. Okay, this is the Ask Me Anything. So this is the wizard’s workshop. I love an alliteration, come on. This is amazing. It’s fabulous. And it’s magic related. And it is spooky season. So don’t be afraid of exist statements.

We do have a lot of events coming up as well. So we’ve got customers presenting all through like November.

I mean, don’t forget tomorrow. There’s an Ask Me Anything. Again, speaking of alliteration, system smarts and strategic starts, Monique and Kirsten have their work. I’ve seen some of the questions. Sign up, go out. They’re really good.

If you want to see the scale team in person, we’re gonna be in Chicago, November 12th. So we’ll put that link in there. But we absolutely would love to say, hey, we’re gonna be talking about governance, adoption, all of that good stuff and sharing great tips. Now, this will be in the follow-up email as well. I mentioned earlier, we are doing 2026 planning. Obviously, we’re trying to bring back like Leap style content if you know what Leap is, you know, it’s amazing. So this is the kind of information we want from y’all is like on our programming, how do you want to be communicated to all that good stuff. We’ll put that link in the follow-up email, but you’ll also have a link to this. Please shout out Sky and Nathan, please. So when you get this survey, it’s just like five questions, but we would love like to say how wonderful this presentation was. And I think that brings us straight to our Q&A. So really quickly to Sky and Nathan. So there’s a bunch of questions in terms of like, can we do this? Can we do that? And I just want y’all to talk about if there’s any objects that you like, either that they’d have to avoid or just won’t work. So I’m thinking, you know, obviously you can use Exist Statements for custom form fields, right? So are there objects that they couldn’t use it for? Cause I know you want to give them kind of a high level understanding of what they can do and what they can’t do.

If you can’t do a normal filter with that object, then it’s not going to work in the Exist. So I think I was just looking through the questions, but I think I saw someone mention like rich text or something like that. Rich text is one of them. Yeah, you’re going to run into problems there.

Yeah, you got to understand there’s some intermediate stuff that’ll cause some problems, right? You need to understand how a multi-select field works when you’re trying to filter and stuff. That’s one that you learned the hard way, the multi-select field.

So that’s a good one. So I just wanted to bring that up. I don’t know if anyone in the last three minutes, like I’m going to collect all the questions and send them to Sky Nathan afterwards and see if we want to post any in the experience post for this event, or if we want to maybe put them in the AMA. But does anyone want to come off mute and ask their question in the next couple of minutes? You got three minutes, don’t waste it.

Or maybe you just blew everyone’s mind. Oh wait, Krista.

Hello, she’s got to click that accept.

Krista, are you there? It worked, yes. It done, okay, yay, there we are. There wasn’t an accept, but I did unclick the mic. So I just was wondering if there is a way to pull up the expression fields in calculated fields. So if I wanted a list of all of the expressions we’ve put in the backend, can I run a report on here are all my expressions and then which calculated field is that expression in? The reason I’m asking is because sometimes I need to know if a certain field is being used in one.

Nathan, if you need a little bit of time to think about it, I just want to jump in at the beginning and say one point of governance for me is I try to use the same expression for the same calculated field. But regardless, whether I’m using the same expression or a bunch of different expressions, every time I make a calculated field expression, I put it in the description.

This way, when I run a report on my calculated field, I can pull in the descriptions a lot easier than whatever it is Nathan’s gonna tell us.

Yeah, and you can filter on description pretty easy, right? Does it contain this? Yeah, exactly. So that’s perfect. So yeah, I think that’s a good example of you probably don’t need an exist for that. However, I don’t actually think it works with calculated fields, but you can actually run a report report with an exist statement that looks at any report that had used any text in a filter. So I’ve built one of those before, it was kind of fun.

So very similar thought process, but I don’t think we store the actual expressions in the database. We do, but it’s like several layers in. This is more advanced reporting unless exists. But Cynthia, if you want to collect that, we can post it. Oh yeah, got that one right now. Cool. Yeah, excellent. All right, next step is- Thanks, Crystal. Tibor. Tibor. Hey guys, thank you. I understand that in the task reports, I can jump multiple levels up to a, so to say. So instead of just checking the project, I can check the program or the portfolio. I wonder if it goes the other way around as well. So in a project report, can I set a field that says, show me projects that have tasks that match the following conditions? How do I do that? What’s the linking object there? I actually like building it that way. Yeah, I think it’s still the same thing, but it’s flipped now, isn’t it? Because if you’re in a project report and you’re looking for tasks, it’s the linking line is field ID is equal to project ID instead of the other way around.

To me, and then the left side is project ID from the task.

Yeah, and I have an example right now.

Because I mean, the challenge I’m having is on the project object, there’s no fields that would be a foreign key for a task because it’s the other way around.

And that’s okay. As long as you can build that link in one direction, you can build a linking line. So let me show you real quick.

So it would be, and I guess I should share my screen. That would be.

And if we need to, yeah, we need to do a screenshot. Yeah, let me see if I can do it really quick. We can do that too. Otherwise we can follow up. We are two minutes over. Okay, so if we’re on the project.

So collections, there’s tasks, right? And then we go there and then we got task ID. But yeah, there’s no foreign key to task on project, right? I think the foreign key is still going to be project ID versus field, versus ID. Yeah, so in our example where we were doing task and going up on task report, we had it be tasks ID on the left side and on the right side it was field ID. And then in this case, it’ll be right side field ID and left side will be project ID. So we’re still building that link between the two.

And I can drop.

I’m just pulling up an example. We can go to the next one and I will get this example to you real quick. We’ll do one more. I know everyone, you know, many people have to drop, but we’ll do, Jason, I know you’ve had your hand up for a bit. So if you wanna ask your question and then I will post this on experience league and I’ll tag Sky and Nathan. So like we can keep this conversation going, but again, don’t forget there’s homework and an AMA coming up. So go ahead, Jason, sorry. Thank you guys. Good to hear your help.

I had a question on whenever we were kind of doing the brute force way and we’re like listing all the different IDs, is there a way instead of getting IDs to get names to where you could actually see the values? Cause I know sometimes you can like do like take out the ID, do some I call the name or something, but some of those I wasn’t sure if it would interchange. Is there a way to do that where you get actual values instead of numbers like text? Sometimes, but not for the accessor. So not for the sharing. We went two or three or five rounds in my case, cause I just couldn’t believe that you couldn’t do it. It all goes right back to the API Explorer, Jason. So if you go to the API Explorer, type in share, and then you have access rules set up again, anything that is in the reference tab of that sharing is fair game. So you will notice right away that for sharing, the reference tab is either blank or it’s very sparse. If there was a way to get the accessor name, it would have showed up in the reference. You would have been able to see accessor. You would have clicked on that and gone to a bunch of different places. For sharing, it just isn’t there. For other objects though, if it’s there, then you can definitely use it.

Thank you.

Awesome.

Nathan, Skye, thank you so much. First of all, the chat is so delightful. When you get a chance later, you’re just gonna be, everyone loves you both. So thank you so much for doing this. I know five times, but you only like, you missed a bunch of the conversations, Skye, where people were like, if Skye said it, we believe it.

Yeah, and y’all are amazing. So thank you so much for this. Cannot wait for the AMA. I’ll get everything posted today, get the followup out this afternoon so that you can start doing your homework.

But just want to say thank you both again. I know how much work you put into this, and I’m really grateful. Thank you. And thanks for everybody coming.

Thanks for organizing. Yeah, y’all have a great day. Don’t forget about the AMA tomorrow, and we’ll see you next time. All right, see you soon. Thank you very much. See you. Thank you.