Creating a Database view joining Requested Item and Attachments table

[Dan R]

Hey SNC,

The business requirement I have is to create a related list that shows records on the sc_req_item table (RITM's) and their associated attachment.

To do this I'm trying to create a database view joining the sc_req_item table with the sys_attachment table, but for some reason I can't figure out why it's not working. I can join the task table to the sys_attachment table with no problem, and I can also see that the sc_req_item table has been joined in some out-of-the-box provided database views, but when I try to join sc_req_item with sys_attachment, no data appears.

The reason I need to join the sc_req_item table instead of the task table is so I can create a view of the database view that filters on the catalog item. When I join the task and sys_attachment table, I'm unable to figure out how to filter the data by cat_item.

Please see screenshots below. Any idea what i'm doing wrong here? Any information is greatly appreciated!!

Thanks ,

Dan R

Database view to join sc_req_item with sys_attachment - not working

table shows no records:

Database View to join task with sys_attachment - working

table shows records:

OOB Database view that shows sc_req_item joined with task_sla:

[Ashley Snyder1]

Database views are mainly used for reporting. 

"A database view defines table joins for reporting purposes."

https://docs.servicenow.com/bundle/orlando-platform-administration/page/use/reporting/concept/c_Data...

You should be able to show the attachments related list out of the box on a sc_req_item form, I can see in my PDI:

Can you explain more why this would not meet your requirement?

[Dan R]

Hi Ashley thanks for responding,

My goal is not to show attachments on the current ticket, it's to show a list of all tickets and their associated attachment where cat_item = xyz. 

The second step of this is to provide an easy way to view this table and I was hoping I could do it as a related list on the sc_task. Alternatively I've thought of creating a dashboard with the view, but a related list seems a bit cleaner.

[Jaspal Singh · Accepted solution]

Hi Dan,

Database view is mostly used for reporting purposes. You case demands creation of new Relationship so as to display it in Related list.

You need to create Relationship as below from System Definition >> Relationships. You may refer article link for a check that has detailed steps for configuration as well.

Now, for the database view part that does not work 

For 1.Database view to join sc_req_item with sys_attachment - 

Try changing the order of sc_req_item to 200  & replace

attachment_table_sys_id=item_sys_id

with

attachment.table_sys_id=item.sys_id

for a check

[Dan R]

Thanks Jaspal,

this worked well.