EXISTS for all projects in a program if one of those projects has a certain value? | Community
Skip to main content
-JC
-JC
Level 6
May 31, 2023
Solved

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

  • May 31, 2023
  • 3 replies
  • 1291 views

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? 

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by William--

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

 

3 replies

Madalyn_Destafney
Community Advisor
Madalyn_DestafneyCommunity Advisor
Community Advisor
May 31, 2023

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! : )

If this helped you, please mark correct to help others : )
    skyehansen
    Community Advisor
    skyehansenCommunity Advisor
    June 1, 2023

    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-projects-workers-have-open-tasks/m-p/593647#M53665 ) 

     

    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
      Community Advisor
      RandyRobertsCommunity Advisor
      Community Advisor
      June 1, 2023

      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.

        -JC
        -JCAuthor
        Level 6
        June 7, 2023

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

        William--
        Community Advisor
        William--Community AdvisorAccepted solution
        Community Advisor
        June 2, 2023

        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

         

        If you like my content, please take a moment to view and vote on my Idea Requests: https://tinyurl.com/4rbpr7hf
          -JC
          -JCAuthor
          Level 6
          June 7, 2023

          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.

          Terms & ConditionsAccessibility statement

          Loading footer...