How to create Database View to join cert_element and incident table?

Dennis25 · ‎04-24-2022

Hello,

I have set up Data Certification for certain fields from Incident table.

As you can see below, by reporting only on cert_element table, I am not able to add in more fields from incident table (i.e: incident.priority, incident.assignment group, incident.service_offering).

So I thought of creating a Database View to join cert_element and incident table to get the fields I need from incident table.

Which fields do I use to join cert_element table and incident table using Database View?

I have tried as below.

But getting error that ce.id is unknown.

I have checked in the cert_element table that it should be there.

ce.id is actually as below:

Any ideas here are greatly appreciated.

Thanks in advance.

Aman Kumar S · ‎04-24-2022

Hey Dennis,

Found one related article on HI:

Description

When creating a database view it is sometimes necessary to join tables via a Document ID type field.

Like Reference fields, Document ID fields allow the record to reference another record. However, Document ID fields allow you to reference a record on any table, not just a pre-defined one like Reference fields.

This means that the data is stored in 2 fields: Table Name & Document (sys_id)

Incorrectly joining tables by only referencing the Document field can lead to unexpected outcomes like only returning partial results or, a mismatch between the row count and the results returned.

Ref link:

https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0952022

Best Regards
Aman Kumar

View solution in original post

Dennis25 · ‎04-24-2022

Many thanks to Ankur and Aman !

Aman Kumar S · ‎04-24-2022

Glad to help 🙂

Best Regards
Aman Kumar