Create EXISTS filters for complex reporting

Transcript

Today I’m going to explain to you what an exist filter is, what it can do for you, and how to build one from scratch. We’re going to use the proof approval report. And I’m trying to make a proof approval report that is being filtered by projects that are in the status of current. So these are proof approvals that are on projects that are current.

Seems simple enough, right? But we’re about to run into a few issues. So let me start from scratch here.

Let’s say that I wanted to build this filter in the UI. Unfortunately the proof approval report doesn’t really allow us to go very far. A lot of people filter by approvers, but when we’re trying to actually find it on an object, we run into a lot of problems. The highest level object that we can even go to is the document version. And from there we can get document ID. Not the most helpful if we’re trying to get to projects in the status of current.

So if I switch to text mode, usually we can go a little bit further, but in this case this is actually about as far as we can go. We can go up to the document version table, so one jump, and then we can get a field on that table. If there was no jump limitation, this is the way that it would look. I would go document version, then I would jump up to the document table, then I would go to the project table, then I would go to the status field on the project table, equals current. And to make the text mode work, I would just grab all of this here, and I forgot the S, and I would put mod equals in. So this is an equals filter, so as long as the status is current, everything should work. There is no syntax error, and if I hit save and close, we run into an error. And the reason for this error is because of the table jumps. As I mentioned before, we could go one table jump and then get a field on that, but we can’t do a table jump, table jump, table jump, and then field.

So how do we bridge that gap? How do we get all the way up to the project status equals current? Because we can go up one table jump and then grab a field on that, looking at this, which table should we be on if we want to filter by just project status equals current? It would be the document table. So if we could somehow get to the document table, we could make this all work.

Now, that could be done in a document report. In a document report, we would be able to just set a filter, project status equals current. We could even use the UI. We wouldn’t need text mode, and everything would be great.

We’re going to do the same thing here by using exists. We are going to link prove approvals with documents so that we can go from the document table and just do this simple filter here. So let me show you what that looks like.

OK, so I put all of this with exists colon one before it and having those all together and with the same number or letter. I could do a I could do B I could do five right as long as they’re all the same. Then they’re going to be grouped together as one single exists exists filter.

Now, what I need to do is I need to get to the documents table. And so that’s as simple as putting in the object code equals docu. So this row is finished. Now, the object code. So that is called as the linking object or the destination object. Depends on how far we have to go for the exists filter. And oftentimes the linking object and this in this case, it is linking because we are using it to link us between project and proof approval. We have to go up to the document and then we can go from document to project status equals current.

The linking object code or this object code we can find in the API Explorer by going to the table. This is the document table and getting this code right here. So everything we see on the far right, that is the code that we use for object code.

Now, to link proof approvals to document so that we are on the document table and then we can finish this filter. We do have to have a formula here. It is really simple. It is an equal sign. And on the left hand side, we need some kind of ID, some some field on the documents table. On the right hand side, we need some kind of field or an ID. ID’s are the best because if they match perfectly, we know we have connected the objects. So an ID field on the proof approval will be on the right side. So this is what this will look like on the left hand side. We are going to do documents.

So if we are thinking between proof approval and documents. So let me show you what that looks like. If we start at proof approval. OK, so this is a table. These are the fields on that table. This reference allows us to go up to a different additional tables through to like a key right through through ID. So if we have document version ID on the proof approval, it allows us to connect to the document version table.

You know, to the ID on that document version table. And then same thing here. We have document ID allows us to connect or reference document and it takes us to the document table.

So we want to find a field on the document table that would also be referenceable on the proof approval table. And right here, current version ID is the one that I have used successfully and I really like. So if we do current version ID equals, so we’re done with the left side. Now we want this to equal something on the proof approval side. Now we always put field. Field is a stand in for whatever object we’re currently on. So we’re in a proof approval report. Field equals proof approval. That is the table that we’re on. And what field on the proof approval would equal the current version ID? Proof approval. We could go up to document version and get the ID or we could just grab it here. Document version ID. So as long as the proof approval is on a document version and that ID matches the current version of this document, we can then go from that document and finish that filter and do. This is just the filter part. This is all the linking stuff that gets us to a point where we can just filter on project status.

Okay, I’m going to go ahead and save this and we’re just going to make sure it works. I have four proof approvals. We have proof creator, proof owner, right. Inversions. If we click on this document. Okay. Took us to the document name. We can click on project. We see it’s in a status of current. If we change that to something else. Go back. We would expect that to be gone because now there we go. So it looks like there were actually two fields that were on the two proof approvals that were on that project. That is no longer in current. And so they fell off.