Task Report Filter - Compare Assignments to Project Sponsor

[KellieGardner]

I'm trying to find a way to run a task report that will show me when a Project Sponsor is not one of the users assigned to the task in that project.

These do not work

assignmentsUsersMM:ID=FIELD:projectSponsorID
assignmentsUsersMM:ID_Mod=notin


assignmentsUsersMM:ID=FIELD:project:sponsorID
assignmentsUsersMM:ID_Mod=notin

I'm not sure if it's possible since the Assignments is a collection of data on the task, and ultimately, it's too many jumps to get to the project sponsor ID?

Any suggestions?

[JustinRenteria2]

Hmm I havent tested this but perhaps you can create a calculated field on the Task custom form that references the project:sponsorID field. I'm still iffy on whether the Assignment "collection" will allow this comparison though.  

[Sven-iX]

HI @KellieGardner 
See if this works for your usecase

EXISTS:A:$$EXISTSMOD=NOTEXISTS
EXISTS:A:$$OBJCODE=PROJ
EXISTS:A:ID=FIELD:projectID
EXISTS:A:sponsorID=FIELD:assignedToID

In my test is seems to work, pulling up ONLY tasks to which the sponsor is NOT assigned. 

[KellieNYL]

Thanks, @Sven-iX. Unfortunately, it doesn't work since the assignedToID only looks for the primary assignee, and the sponsor could be any of the assignees.

[Sven-iX]

I'm embarrassed - of course my 2 test tasks had indeed the sponsor as primary 😞 
@MoniqueEvans I tried this to now avail

assignmentsListString=project:sponsorID
assignmentsListString_Mod=cicontains

I also tried combining exists clauses but so far no luck 😞  

[Sven-iX]

@KellieGardner is it feasible to create a task custom field {project}.{sponsorID}?
Then we can do a a FIELD comparison that should work. 

[MoniqueEvans]

I was thinking this route as well, but instead of using the PROJ object code, try the project user (PRTU) object code; however that would assume the user is only listed on that task and no where else.

Throwing spaghetti on the wall...

What about assignmentsListString instead of assignedToID?

[Sven-iX · Accepted solution]

Hi @KellieGardner 

try this: it's an assignment report. It assumes you're interested in a particular task on the project (hence the name check).
The only downside is that it'll show all assignments without the sponsor - so you'd have to group by project.  

assignedToID_Mod=notblank
EXISTS:2:$$EXISTSMOD=NOTEXISTS
EXISTS:2:$$OBJCODE=PROJ
EXISTS:2:ID=FIELD:projectID
EXISTS:2:sponsorID=FIELD:assignedToID
task:name=should have sponsor
task:name_Mod=cicontains
taskID_Mod=notblank

 Also - If you're looking for tasks where the sponsor IS assigned, this works fine:

project:sponsorID=FIELD:assignments:assignedToID
project:sponsorID_Mod=eq

But the inverse doesn't work as expected

project:sponsorID=FIELD:assignments:assignedToID
project:sponsorID_Mod=ne

In general I've found the not-in capabilities lacking 😞 I really think the simplest is the hidden calculated field, then you can do a simple compare at the task level and even colorize the row based on that.

[KellieGardner]

I will try these today! Thanks @Sven-iX. I really admire your diligence.

[Sven-iX]

I love brainteasers 🙂 
@KellieGardner  - another thought: Those task to which a sponsor is supposed to be assigned - if they have a role assignment eg "sponsor" we could check for that : if we find an assignment with that role that has no user assigned to it, then the sponsor is not assigned. 
Let us know what you end up with! 

[kautuk_sahni]

@KellieGardner Just checking in. Were you able to resolve your issue? We’d love to hear how things worked out. If the suggestions above helped, marking a response as correct can guide others with similar questions. And if you found another solution, feel free to share it — your insights could benefit the community. Thanks again for being part of the conversation!