Database view to join history_line & case table

[Karanpreet]

Hi I need to create a database view to use in an Export Set as history line table contains comments in the New field & case has correlation id .

we need to join these to get comments & correlation id in single table 

i have added these 3 tables 

since history line contains set's sys id which contains sys id of record on id field of history Set table.

but iam  stuck on the where else clause.. as i cant see any records.

[Mark Roethof]

Hi there,

Are you sure you want to do this? Using the History Line table? You do realize, that this table does not hold ALL data, it will only hold the data for records on which manually the "History > List" has been clicked upon. If no one did so, or a while ago, then you are looking at incorrect/old/etc data.

[Karanpreet]

Thanks for you reply @Mark Roethof , but the idea is we need to send all the comments added to a case by a user to Navex using Export set.. 

1. we have tried with journal table but we cant see the name of the person who update the comment but only their id.

2. second is this one using history line table , for this solution we would need to create database using the given 3 tables .

3. or the least favourable is to add a custom table to capture all the comments user has added & send record from there.

What should be the best practice in this scenario?

[Mark Roethof]

Again, using the History Line table is not a way to go. It should not even be an option on your list.

Please look into and understand the purpose of this table. You can go ahead... though you will face what I already mentioned: outdated data / incorrect data / etcetera.

[Brad Bowman]

Per the ServiceNow docs, it is not recommended to use history sets. You can use Audit (sys_audit) table which has the same data as history and history sets tables.

https://docs.servicenow.com/bundle/vancouver-platform-security/page/administer/security/concept/c_Hi....

https://docs.servicenow.com/bundle/vancouver-platform-security/page/administer/security/concept/c_Di...

https://www.servicenow.com/community/developer-forum/database-view-for-history-and-record-history-ta...

If you want to join the Case table and Audit table, it would look like this: