Relate the Task table to the sys user has role table

[bekfro]

I'm trying to report on the ITIL users who do not have any tasks assigned to them.

Trying to use the related list condition of "None"

How do I relate Tasks->Assigned to to the sys_user_has_role?

[GlideFather]

Hi @bekfro,

let's clear this slight confusion, sys_user_has_role is table that stores a User and Role.

You should refer to Task table and dot walked maybe? I don't have a chance to replicate myself but I guess the table you mentioned is not correct for this

[aruncr0122]

Hi @bekfro ,

To Achieve your requirement, you can try the following. It worked for me.

1)Start the report from the User [sys_user] table instead of sys_user_has_role.

2)Add a condition: Roles → Role is ITIL. (This will leverage the related list filter and pull only ITIL users.)

3)Add a related list condition: Tasks → Assigned to is None.

This way, you’re asking “Give me all Users who have the ITIL role, and also have no related Tasks.”

[aruncr0122]

Alternately you can try below approach aswell.

Create a database view

If you specifically want to join sys_user_has_role and task, you’ll need a database view:

Table 1: sys_user_has_role

Table 2: sys_user (join on sys_user_has_role.user = sys_user.sys_id)

Table 3: task (join on task.assigned_to = sys_user.sys_id)

Then you can build a report off that view and filter by “Role = ITIL” and “Task is None.”

[bekfro · Accepted solution]

Thank you, @aruncr0122, this is exactly what I did after I posted the questions  🙂  The issue I was running into was that the field "Roles" on the user table had an ACL so only admins could see it.