Filter document report based on custom field at project level

[amybillmayer]

Hi all - I'm working on some "Team" dashboards. I have a custom field at the project level that identifies which team the project is assigned to (Assigned to Team). Two of the reports I need to create for these dashboards are 1+ week pending Document approvals and 1+ week pending Proof approvals.

I've figured out how to pull the Team name into a column on these reports:

Proofs:

displayname=Team
linkedname=direct
namekey=documentID
textmode=true
valuefield=documentVersion:document:project:DE:Assigned to Team
valueformat=HTML

Documents:

displayname=Team
linkedname=direct
namekey=document
querysort=document
textmode=true
valuefield=document:project:DE:Assigned to Team
valueformat=HTML

However, what I really need to do is create a report for each team, using the Assigned to Team field as a filter. This is where I'm stuck. I'm trying something like this, but it just breaks the report:

document:project:DE:Assigned to Team=ALFS
document:project:DE:Assigned to Team_Mod=cicontains
requestDate=$$TODAY-1w
requestDate_Mod=lt
status=NEW
status_Mod=in

Anyone have any suggestions? Thank you in advance, you're always so helpful!

[skyehansen · Accepted solution]

One of Kyna's most useful posts (of all time) is this one:

https://experienceleaguecommunities.adobe.com/t5/workfront-questions/quick-tip-how-to-modify-documen...

I would recommend maybe amending one of her or my code snippets to get what you need. I have one that starts off with 

EXISTS:1:$$OBJCODE=PROJ
EXISTS:1:documents:ID=FIELD:documentID

(first two lines: there exists a project with a document where your doc approval's documentID matches the project's documentID)

Just a guess, but to this you might consider adding:

EXISTS:1:DE:Assigned to Team=ALFS
EXISTS:1:DE:Assigned to Team_Mod=cicontains

(second two lines: once you've found such a project, make sure the assigned to team is ALFS)

Note for more advanced users: I was really delighted at the time to find evidence that you could use a collection (2 collections?) in an exists statement.

[amybillmayer]

Thank you @skyehansen - your real-world examples made all the difference in the world. I'm not super familiar with the EXISTS functionality, but this made it much easier to understand. For anyone else who this may help, this is the final code and it works like a charm:

Proofs:

EXISTS:1:$$OBJCODE=PROJ
EXISTS:1:DE:Assigned to Team=ALFS
EXISTS:1:DE:Assigned to Team_Mod=cicontains
EXISTS:1:documents:currentVersionID=FIELD:documentVersionID

Documents:

EXISTS:1:$$OBJCODE=PROJ
EXISTS:1:DE:Assigned to Team=ALFS
EXISTS:1:DE:Assigned to Team_Mod=cicontains
EXISTS:1:documents:ID=FIELD:documentID