Hello Community,
I hope you can support me here.
I've prepared the Data Certification process to request Managers' confirmation on their team member roles.
We wanted them to verify who really needs the following roles:
- itil
- approver_user
- business_stakeholder
- Case Agent (custom)
To achieve that I designed the Data Certification process around [sys_user_has_role] table so that I could get Users + their role and I selected Users.Manager as the Assignee of the task.
We have our results in the [cert_element] table but I didn't foresee the following problem --> I can't see details about users affected by their Managers' decisions because it is a Document ID field and I can't dot walk on that field to get information about users.
If I click the Document ID field I can see the desired details but I can't display them on the report.
My goal is to see the Certification Task.Assigned To (Manager) and the decision which was provided for User's role.
I was thinking about database view but I don't know what tables and how to connect them to get the results. I was thinking about [cert_element] + [sys_user_has_role] but I can't see anything that would connect them.
When I enter Document ID field I can see the sys_id of the User in the URL, I was thinking about using it to connect tables but I don't know how to extract it so I can actually use it.
Any ideas on how to achieve the report I am looking for?
Thanks
You can do this with a database view on cert_element and sys_user_has_role joining on the document field like this:
You can display User table columns by dot-walking from the user field on the sys_user_has_role table, or include sys_user in your view if you want, joined on role_user = usr_sys_id
