EXISTS Report HELP!!! (Connecting Proof Approval to Project)

[MoniqueEvans]

I am working on a new fun challenge and could use some help from my team aka this Community.

I want to create a report that tells the story of how long approvals have been taking for a series of projects. The projects are within a few programs but there is also a robust custom form attached so there are many ways to filter to get the specific project list.

Does anyone know of a way to do an EXISTS statement where I can connect Proof Approval to the Project? I know I can get to Document but not sure if I can continue to move up the hierarchy.

I'm open to any and every idea!

[The_Real_Melinda_Layten · Accepted solution]

Unfortunately your connection to the project is all the way back on the document, and you can go from documents to document versions and document approvals but the proofs are listed on document versions as a string and not an object, so you can't reference information on proofs from a document/document version report.  

Proof approvals do have a reference to document version, so it's possible you can reference up  from there to document and to project, so consider if you can start at Proof Approval Report, it doesn't look like it's set up for referencing that information directly.  So maybe mock it up with the document version report and  then add things to text mode with the additional level of going from proof approval to document version.

Not an easy one! 

[MoniqueEvans]

This is precisely what I was thinking; thanks Melina for confirming I'm on the right path!! Creating a report to capture the document approval information within the given project selection was tricky enough, to get all the data I created a Document report with an approvals collection column that I can now manipulate in Excel. Hopefully, a Document Version report can get me the Proof info I need 

[Surya_Lakhani]

Hi Monique,

Here is a filter I had used for a proof approval report to only show proofs that are associated current projects: 

EXISTS:a:$$OBJCODE=DOCU
EXISTS:a:project:status=CUR
EXISTS:a:project:status_Mod=in
EXISTS:a:versions:ID=FIELD:documentVersionID

[MoniqueEvans]

Thanks, @Surya_Lakhani! This is a helpful text mode filter to keep in the library. Unfortunately, it doesn't help my specific need. I'm looking to use project custom data fields to narrow down the list of Proofs to just those in given projects.

This does remind me that if all else fails I could use a project report to get the list of project ID's and then create an EXISTS filter with project:ID= 

[Surya_Lakhani]

You should be able to switch status with the custom filed you are trying to filter on. Just also update the '_Mod=' accordingly.