What incident was used to create the Change request.

[awyatt]

The change request table doesn't seem to have a relationship with the incident table that will provide the incident that created the change(incident elevated to change not using a problem). And/or all incidents that will be solved by the change. I attempted to use the "related incident" field in the change request table. But it's null when even when there is an incident that will be solved by the change. I don't think I understand the tables and there columns. Is the a definition resource somewhere to explain the contents of the fields and there meanings or are we to just use the field name and schema map?

Thanks for any help

Alan  

[Steven G_]

Hi Alan,

I would check to make sure you have the appropriate Database View to pull the data back on reporting

[Goran WitchDoc]

Hi Alan,

Ahh. then there is a problem. This is because there isn't a actually field on change request with the incident number. The related list is pretty much just a filtered list with all incidents that has the current change sys_id in the change_request field.

So, short version is that you would need a DB view of change_request and incident. But I'm kind of curious what the end result is for this report. Why do you need this? Do you want to have a report of all changes created by incidents? or what is the purpose? And what are you going to do with the result?

Perhaps we can give you an even better option here.

[awyatt]

The end state is to generate two reports. One with all changes that are related to a incident(s). Meaning they were submitted by customers based on some problem they have. The second is to generate a report of changes that were not created from a incident. Meaning they were created in-house by developers or managers that saw a change or additional functionality that is needed. This will help to prioritize and categorize changes. I think I need a database view. I just don't know where to connect the two tables as the incident number for changes is in related list. Not a db field and I don't have a field in the change_request table to make the join. I wounder how ServiceNow does it in populating the related list.

Thanks

Alan

[DrewW · Accepted solution]

A database view can involve more than two tables.   In this case you simply need to create a database view between the change request and incident table that uses the Change field on the incident table to "join" to the sys_id field in the change request table.   Make sure its a left join with the change request table on the left.

Create a database view

You can add as many tables as you want.   just remember reference fields used to link to the table they point at use the sys_id column in the referenced table.

[awyatt]

This was the answer. By creating a database view and joining the Change Request "sys_id" field to the incident table change request field (which is column name "rfc")   I was able to pull all change request and there associated incidents.

Thank you ALL very much for helping me to solve this issue!

Alan