Database view and Document ID

[dp11]

Hello all,

I have been scanning the community for all articles related to Document ID with the goal of obtaining the sys_id from that type of value. And it seems its not that simple. One suggestion in the following post was:

"I don't think you can get sysId directly of the record being referred by Document ID field....

you can try to create database view table and then export and then it might contain sysId"

https://www.servicenow.com/community/developer-forum/export-the-sys-id-of-document-id-column-instead...

I am trying that approach. Here is my database view:

So, I am trying to get all email attachments that were discarded. Then obtaining the email referenced in those. And obtaining the task (specifically Ticket) referenced in the email's 'instance' field (which is of Document ID type). 

However no record is being returned and I have 101 records that satisfy the conditions. Can anyone see what is wrong here?

Also, this is very similar to the OOTB incident_metric one and that works fine:

Note: The mi_definition field is of type Document ID.

Appreciate any help.

Thanks,

Deb

[Rushi Savarkar]

I saw a space in each Where Clause conditions

Remove space in each Where Clause like below

emailatt_action='discarded'

emailatt_email=em_sys_id

em_instance=tkt_sys_id

[dp11]

OK, did that but no change in outcome still 😞

[Ankur Bawiskar]

@dp11 

if you are using document id field then there is an extra step you need to take i.e. you need to give table as well

Database view - How to correctly join a table using a document_id field 

add this in the 3rd where clause

mi_id = inc_sys_id && mi_table = 'incident'

If my response helped please mark it correct and close the thread so that it benefits future readers.

[dp11]

Thanks for your reply. As per your suggestion I added the table name but still no output:

That last where clause condition is: 

email_instance = tkt_sys_id && email_target_table = 'ticket'

[Ankur Bawiskar]

@dp11 

Few suggestions

1) make the variable prefix upto 3 chars

2) which table is that ticket ? are you using correct field name from that table

3) are you sure the action field has value Discarded in it?

If my response helped please mark it correct and close the thread so that it benefits future readers.