Database views

antonio30 · ‎01-12-2017

I am attempting to combine the Incident table and the Task table to create a custom report in ServiceNow. When reporting using the Database view I can pull most of the information except the additional comments and work notes don't come over. The fields display but they are empty. I can use a report from the Incident table and the additional comments and work notes display just fine. Any thoughts or ideas on what I may be doing wrong with my Database view?

drjohnchun · ‎01-13-2017

OK, that helps. First, you're creating a view using Incident and Task SLA, not Task - I see that this caused confusion in the replies (Task SLA is NOT extended from Task, so you need a database view to join them). Second, you don't need to use sys_journal_field (unless there are reasons I'm not aware of).

I have recreated the view as follows and it shows values for Additional comments, Work notes, and Comments and Work notes as you can see below:

View solution in original post

antonio30 · ‎01-16-2017

John,

Good afternoon I took screenshots of what happens. Please see below.

When using the setup below I can see comments and work notes, but additional comments are blank.

drjohnchun · ‎01-16-2017

Can you add Work notes column in your screenshots? I want to confirm which field the data came from for Comments and Work notes (since this field combines data from both Additional comments and Work notes) - the blank Additional comments may be OK if only Work notes field was filled in. For all our tests, it'll be best to show all 3 fields (Additional comments, Work notes, Comments and Work notes) side-by-side so we can quickly rule things out.

antonio30 · ‎01-16-2017

drjohnchun · ‎01-16-2017

From this screenshot, I see that you have Work notes, but they're only showing up in the Comments and Work notes column, not in the Work notes column. I have the same Database View, but can't reproduce what you're (not) seeing.

Since at least the Comments and Work notes column is working, can you use it instead?
Which release are you on?
Can you send me the update set for the Database View (just the Database View and the 2 View Tables rows)? You can also Export XML for the 3 records and attach here.

kanika11 · ‎01-11-2018

hello John

I myself created one database view to combine task sla table and incident table but i didn't get comments in output.

I also tried combining sys_journal_field table and incident table. Here I am able to get all comments for incidents. However suppose we have 3 comments on "x" incident. so I am getting this incident detail 3 times in SOAP ui. My requirement is to get only the latest comment (highest value for sys_created_on).

Can you please suggest some suggestions which I can follow to update my database view