Relate the Task table to the sys user has role table
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
2 hours ago
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
an hour ago
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
/* If my response wasn’t a total disaster ↙️ ⭐ drop a Kudos or Accept as Solution ✅ ↘️ Cheers! */
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
an hour ago
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.”
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
an hour ago
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.”