How to get only projects in which the viewer is assigned a task on a task report.

[MichaelPe1]

I have a task report that is grouped by project name. The purpose of the report is to display 3 specific tasks for each project (Task A, B, C). I want to make it only display the projects that the viewer has been assigned one of these tasks. How would I accomplish that? I tried the assignment code BUT then it only displayed which task they are assigned to. I want all 3 tasks to show but only for projects that they are assigned to one of the 3 tasks.

Here is the code I am using right now:

OR:1:name=Task B

OR:1:name_Mod=cicontains

OR:1:project:categoryID=6172bf5b00160dbaf36981367b32dcef

OR:1:project:categoryID_Mod=in

OR:1:project:status=CUR PLN

OR:1:project:status_Mod=in

OR:2:name=Task C

OR:2:name_Mod=cicontains

OR:2:project:categoryID=6172bf5b00160dbaf36981367b32dcef

OR:2:project:categoryID_Mod=in

OR:2:project:status=CUR PLN

OR:2:project:status_Mod=in

name=Task A

name_Mod=cicontains

project:categoryID=6172bf5b00160dbaf36981367b32dcef

project:categoryID_Mod=in

project:status=CUR PLN

project:status_Mod=in

[ChloeRo · Accepted solution]

You could consider adding the filter for Project Users >> User ID equals $$USER.ID. The only caveat is that if they're assigned to Task D, but not A, B, or C, that project will still show up on their report because they're technically users on the project, just not on the specific tasks you're looking for.

Adding the Project Users filter would probably help limit your results, but depending on your workflows, it might not completely solve the problem. Not sure if there's any other solution though.

[MichaelPe1]

I think this will work for our process. I will do some testing. Thank you for your help.

[Doug_Den_Hoed__AtAppStore]

Hi Tracy,

I like Chloe's proxy (and would also suggest adding the Assignments column to the view and invite users to Find Themselves), but if you need a precise solution believe you could filter for all three Tasks in question and use an EXISTS statements to retrieve (only and all) Projects where ANY of those three Tasks in question is assigned to the currently logged in user.

Batteries not included. Some assembly (and lots of testing) required.

Regards,

Doug

[ChloeRo]

Just because I am a glutton for punishment curious, I tried to make the EXIST filters that Doug alluded to. I think I have it down, but just be warned, the report was taking a while to load. I'm not super confident in building EXIST filters, so there's a good chance that this is clunkier than it needs to be.

If Project Users does the trick for you, I'd recommend just sticking with that, solely so you can use Standard Mode (you can't use Standard Mode with EXIST filters - and if you toggle back and forth from Text Mode to Standard, back to Text, it clears your EXIST filters). So for the sake of other people potentially needing to read/maintain/take over your report at some point in the future, save them the headache. But if you MUST have the EXIST filters, here's the chunk that would need to be added to each set of conditions that you have:

EXISTS:1:$$OBJCODE=ASSGN

EXISTS:1:assignedToID=$$USER.ID

EXISTS:1:assignedToID_Mod=eq

EXISTS:1:projectID=FIELD:projectID

EXISTS:1:task:name=Task A

EXISTS:1:task:name_Mod=cicontains

EXISTS:2:$$OBJCODE=ASSGN

EXISTS:2:assignedToID=$$USER.ID

EXISTS:2:assignedToID_Mod=eq

EXISTS:2:projectID=FIELD:projectID

EXISTS:2:task:name=Task B

EXISTS:2:task:name_Mod=cicontains

EXISTS:3:$$OBJCODE=ASSGN

EXISTS:3:assignedToID=$$USER.ID

EXISTS:3:assignedToID_Mod=eq

EXISTS:3:projectID=FIELD:projectID

EXISTS:3:task:name=Task C

EXISTS:3:task:name_Mod=cicontains

Using the filters you provided in this thread, this is what your exist filters would look like.

------------------------------

OR:1:name=Task B

OR:1:name_Mod=cicontains

OR:1:project:categoryID=6172bf5b00160dbaf36981367b32dcef

OR:1:project:categoryID_Mod=in

OR:1:project:status=CUR PLN

OR:1:project:status_Mod=in

OR:1:EXISTS:1:$$OBJCODE=ASSGN

OR:1:EXISTS:1:assignedToID=$$USER.ID

OR:1:EXISTS:1:assignedToID_Mod=eq

OR:1:EXISTS:1:projectID=FIELD:projectID

OR:1:EXISTS:1:task:name=Task A

OR:1:EXISTS:1:task:name_Mod=cicontains

OR:1:EXISTS:2:$$OBJCODE=ASSGN

OR:1:EXISTS:2:assignedToID=$$USER.ID

OR:1:EXISTS:2:assignedToID_Mod=eq

OR:1:EXISTS:2:projectID=FIELD:projectID

OR:1:EXISTS:2:task:name=Task B

OR:1:EXISTS:2:task:name_Mod=cicontains

OR:1:EXISTS:3:$$OBJCODE=ASSGN

OR:1:EXISTS:3:assignedToID=$$USER.ID

OR:1:EXISTS:3:assignedToID_Mod=eq

OR:1:EXISTS:3:projectID=FIELD:projectID

OR:1:EXISTS:3:task:name=Task C

OR:1:EXISTS:3:task:name_Mod=cicontains

OR:2:name=Task C

OR:2:name_Mod=cicontains

OR:2:project:categoryID=6172bf5b00160dbaf36981367b32dcef

OR:2:project:categoryID_Mod=in

OR:2:project:status=CUR PLN

OR:2:project:status_Mod=in

OR:2:EXISTS:1:$$OBJCODE=ASSGN

OR:2:EXISTS:1:assignedToID=$$USER.ID

OR:2:EXISTS:1:assignedToID_Mod=eq

OR:2:EXISTS:1:projectID=FIELD:projectID

OR:2:EXISTS:1:task:name=Task A

OR:2:EXISTS:1:task:name_Mod=cicontains

OR:2:EXISTS:2:$$OBJCODE=ASSGN

OR:2:EXISTS:2:assignedToID=$$USER.ID

OR:2:EXISTS:2:assignedToID_Mod=eq

OR:2:EXISTS:2:projectID=FIELD:projectID

OR:2:EXISTS:2:task:name=Task B

OR:2:EXISTS:2:task:name_Mod=cicontains

OR:3:EXISTS:3:$$OBJCODE=ASSGN

OR:3:EXISTS:3:assignedToID=$$USER.ID

OR:3:EXISTS:3:assignedToID_Mod=eq

OR:3:EXISTS:3:projectID=FIELD:projectID

OR:3:EXISTS:3:task:name=Task C

OR:3:EXISTS:3:task:name_Mod=cicontains

name=Task A

name_Mod=cicontains

project:categoryID=6172bf5b00160dbaf36981367b32dcef

project:categoryID_Mod=in

project:status=CUR PLN

project:status_Mod=in

EXISTS:1:$$OBJCODE=ASSGN

EXISTS:1:assignedToID=$$USER.ID

EXISTS:1:assignedToID_Mod=eq

EXISTS:1:projectID=FIELD:projectID

EXISTS:1:task:name=Task A

EXISTS:1:task:name_Mod=cicontains

EXISTS:2:$$OBJCODE=ASSGN

EXISTS:2:assignedToID=$$USER.ID

EXISTS:2:assignedToID_Mod=eq

EXISTS:2:projectID=FIELD:projectID

EXISTS:2:task:name=Task B

EXISTS:2:task:name_Mod=cicontains

EXISTS:3:$$OBJCODE=ASSGN

EXISTS:3:assignedToID=$$USER.ID

EXISTS:3:assignedToID_Mod=eq

EXISTS:3:projectID=FIELD:projectID

EXISTS:3:task:name=Task C

EXISTS:3:task:name_Mod=cicontains

[ChloeRo]

Just had to revise the code in my post because I realized that the EXISTS code needed to have all 3 of the task names listed in each criteria set. And even still, I'm not totally confident that it's going to translate correctly. It seemed to be pulling the right tasks in our environment but I'm not sure if it was just a coincidence.

[Doug_Den_Hoed__AtAppStore]

Brava! Nicely noodled, Chloe!

Yes, this type of unioned-using-ORs-with-EXISTS result is pretty expensive, from a data retrieval perspective, so I'm not surprised that it takes some time to render.

One way to speed it up, if the naming conventions permit, would be to use a _Mod=in rather than a (looser) _Mod=cicontains, either on the Task Name (tough to guarantee) or the Template Task Name (tough to enforce). However, if you could do so, it might be possible to hit all three Tasks with a single EXISTS, which should then run significantly faster.

Regards,

Doug