Database view between incident and change request tables

[_-jasonr-_]

Hello,

I am using a database view (DBV) to create a union/join between the Incident and Change Request tables. Management needs to be able to report against all fields on both tables into one list report.

The DBV sets the Incident (inc) & Change Request (chgreq), ordered 100 inc & 200 chgreq, with a left join on the chgreq.

If I am understanding this article correctly ServiceNow Admin 101: Observations on Database Views, Part I

Then I should be able to link all records on the tables with a where clause of inc_sys_id = chgreq_sys_id. Or am I misunderstanding something the author is explaining?

Any help from the community to help me understand the development process for this would be appreciated.

Thanks,

Jason

[Chuck Tomasi]

Hi Jason,

In order to do a database view, you need to have something in common - a relationship between the two tables. incident and change_request are two separate processes. That's where the 'where clause' comes in. It says "How am I joining these together for you?" These two tables really don't have anything with which to join them together amongst themselves.

You could try to join the task table in there somehow since both incident and change_request have a common parent table. No specifics available, but it's a lead.

Database Views - ServiceNow Wiki

[Chris M3]

What's the requirement you are attempting to accomplish?   If there's a field on one that references the other, a database view isn't needed to look at fields in both.   Database views are usually meant for non-reference relationships between tables

[_-jasonr-_]

Chris,

Management wants to report against all fields on both tables in one report. It would be easier if it was a single field referencing on each table. They want to run a report that displays all columns on each table in one report, and therefore they can export one report to Excel.

Hope this helps,

Jason

[Chris M3]

Are they looking for just all incidents + all changes on one table?   Like a union of the data?   Or are you looking for incident + change that resolved that incident in one row?

First option would be a report on the base task table, which you can dot walk to include all incident and all change fields, and filter off the task type = change or incident

For second, then you probably have a field on incident or change referencing the associated reference.   In that case it's a report off that table, and dot-walking through.