The CreatorCon Call for Content is officially open! Get started here.

Relate the Task table to the sys user has role table

bekfro
Kilo Sage

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?

bekfro_0-1759519638887.png

 



3 REPLIES 3

GlideFather
Tera Patron

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! */


aruncr0122
Mega Guru

 

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_0-1759523392411.png

 

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.”