database view showing duplicate records

[will_smith]

I have setup a database view to join the Incident table, the Assessment metric results [asmt_metric_result] table, and Assessment instances [asmt_assessment_instance]. The problem that I am running into is that all of my results are coming back with the same number, even though the assessment values are different. This tells me the records Please see the below screenshot, and thanks for the assistance!

Here is my database view...

Results with duplicating numbers

[Matt Dodd]

Hi William,

I think some of this depends on what result you are trying to achieve. The Assessment data you are querying does contain multiple metrics (and hence rows of data) for each incident that you are collecting results against. So, the results returned will always have multiple rows showing the same incident (INC0013085 in this case), but each of the Assessment Instance numbers should be unique. I think this is caused by the lack of a unique identifier being used in the join between the asmt_metric_result table and the asmt_assessment_instance table. It looks like you're joining using the source_id and the metric_type columns, which i don't think will be unique (hence causing the repetition).

You can use the 'Tables and Columns' menu item under 'System Definition' to look up the tables and the joins that these tables use (hover over the join and it will tell you which field is used for referring to the other tables.)

A couple of other links to some good material on database views are below also which may help...

ServiceNow - Database Views - YouTube

ServiceNow Admin 101: Observations on Database Views, Part I

ServiceNow Admin 101: Observations on Database Views, Part II    

Hope this helps...

[will_smith]

I will start looking into this material and let you know if I run into any further issues. Thanks a bunch Matt.Dodd

[will_smith]

How would you update the join between the assessment instance table and the incident table? I pulled in the metric result table so I could see the results of the metric, but I may be able to use just the results table and the incident.

I'm not entirely sure what the best way to go about setting up this view is? I am wanting to be able to group/sort surveys by assignment group and view the results.

Goal
Client wants to be able to see the analysts that have Excellent ratings and how many they have.

Stretch Goal - this is where I thought the results are needed
The Oracle group wants to be able to see the comments for the rating so they can give kudos to their team.

[will_smith]

Hi Matt.Dodd, I'm still having problems with this database view. The view results are not grabbing the instance question values, which clearly show up in the assessment record. I am needing some help with this as I'm confused why the database view is not pulling over any string value or value information - when it clearly exists in the table. I appreciate you taking a second look at this.

Assessment showing the value and string value.

Same assessment number as above, but this is the DB View results.

And lastly, here is a screenshot of the database view.