Relating attestation respondents to user IDs

[Jim O_Shea]

I need to report User IDs for respondents on the sn_grc_item table and I'm attempting to do so with a database view.  I'm building a view that relates the Name field on the sys_user table to the Respondents field on the sn_grc_item table.  But the result is showing empty User ID's.   What am I doing wrong?

Thanks,
Jim

[Brad Bowman · Accepted solution]

Hi Jim,

Your screenshot shows a different table, so that's curious.  The respondents field on sn_grc_item is a List field referencing the sys_user table, so you would need to use usr_sys_id in the Where clause instead of usr_name, but since a List can contain more than one record, the standard Where clause pointing to one record on the user table from one sys_id on the Control/Risk table will not work.  Time for a graduate-level implementation of a Database View.  You could do this with a Query Business Rule that runs on the Database View Table, like some hot shot proposed in this post:

https://www.servicenow.com/community/platform-analytics-forum/database-view-list-field/m-p/1262510 

but there is a way to do this without the Business Rule.  I'll use a more common example where I have some data in my PDI - Show the User ID for every user listed on the Watch List of incidents.

!isnull(inc.watch_list)

instr(inc.watch_list, usr.sys_id)>0

So you should be able to replace watch_list with respondents, and of course change the 100 Table, to get similar results.

[Jim O_Shea]

This is exactly what I needed.  Thank you.

FYI...

* u_control_attestation_respondents is the name of my database view.

* My GRC friends have guaranteed me there will always be only one attestation respondent.  But I will keep your additional suggestion close at hand in case this changes.

Thanks,
Jim