EXISTS for all projects in a program if one of those projects has a certain value?

[-Jaye]

Hi all, not sure if this is even possible. I'm trying to write a filter for a project report that says to pull all projects in a program if at least one of those projects has an assignedToID of $$USER.ID. Basically the ask is: if I'm assigned to any task in a project, show me ALL the projects in that program.

Is this possible, and can anybody help write it? Note, we can't rely on Project Users instead of using the assignedToID as we don't regularly update that section if someone is dropped from the project, or accidentally assigned. 

Thank you!

EDIT: or maybe there's a solution using a program report, and then use collections to list all of the project names in another column? 

[Madalyn_Destafney]

Hi there! I'd actually use a task report, not a project report because you're trying to show tasks assigned to $$USER.ID in any project. You can group by program then project or have a column for project and just group by program.
Grouping by program isn't native for task reports, you'll want this text mode in your report for Groupings if you want to group by program then project:
group.0.iscollapsed=true
group.0.linkedname=program
group.0.name=
group.0.namekeyargkey.0=program
group.0.namekeyargkey.1=name
group.0.valuefield=project:program:name
group.0.valueformat=string
group.1.linkedname=project
group.1.namekey=view.relatedcolumn
group.1.namekeyargkey.0=project
group.1.namekeyargkey.1=name
group.1.valuefield=project:name
group.1.valueformat=string
textmode=true

I also recommend using 'assignment users' not 'assigned to' because 'assigned to' only pulls if the person is the primary assignee on the task. Assignment users pulls anyone (helpful if you have any tasks assigned to more than one person).

If you found this helpful, please mark correct to help others! : )

[skyehansen]

there's a part of me that wants to ask why  ... this is pretty problematic -- if the user doesn't have access to see the project, then it won't show up on the list.

But for what you're asking, go ahead and try the below as a column in your view.

The concept here, is that your project report FILTER is meant to only pull in every project your user is on. (However you want to do that is fine by me. It's a very simple filter. Here's an example of one that Melinda just posted last week https://experienceleaguecommunities.adobe.com/t5/workfront-questions/project-filter-to-only-show-pro... ) 

The view (code) will then use any project that you've listed, to list all the other projects in the same program. This will put them all in one cell. I'm pulling in only the name (valuefield), but you can valueexpression whatever you like into that block instead (e.g. "if the project is still active and the due date is this year..." etc.)

valueformat=HTML
textmode=true
type=iterate
listdelimiter=<p>
displayname=All other projects
listmethod=nested(program.projects).lists
valuefield=name

[RandyRoberts]

As a sidenote to that, I like to use <hr> as a delimeter when there are lots of multiline entries in the collection. The line between them helps separate entries visually.

[-Jaye]

Great tip - I definitely forgot this delimiter option existed. BRB while I go update some collection columns to make them more legible!  

[William-- · Accepted solution]

In a project report, the below filter will display all projects in any program(s) where the viewer is assigned to at least one task anywhere in the program. The last two lines are optional and exclude programs where all the viewer's assigned tasks are in a status of Complete.

EXISTS:a:$$EXISTSMOD=EXISTS
EXISTS:a:$$OBJCODE=ASSGN
EXISTS:a:assignedToID=$$USER.ID
EXISTS:a:assignedToID_Mod=eq
EXISTS:a:project:programID=FIELD:programID
EXISTS:a:task:status=CPL
EXISTS:a:task:status_Mod=ne

[-Jaye]

Thank you so much @William-- , this is exactly what I was looking for. The "linking" line was where I was struggling:

EXISTS:a:project:programID=FIELD:programID

Really appreciate your help here!

@skyehansen to your point, we've set our instance up so that everyone has View access to all applicable objects. We were looking to build this in order to give our users an easier way to flag gaps, most specifically projects they should be aligned to or aware of but aren't.