how to get the sys id of a reference field on report of custom table in ServiceNow

[VishakhaShi]

We have a custom table called certificates and there is reference field on the certificates form called Business application. We have a requirement to create a report on certificates table where they want to get the sys id of the business application as a column of the report. 

How to achieve this? 

[Dr Atul G- LNG]

Hi @VishakhaShi 

I'm not sure if you’ll be able to get the sys_id or not, but I’m curious—what exactly do you plan to do with it?

[VishakhaShi]

Hi Atul,

The requirement is requested by one of our clients. They will be using the report so i am not really sure.

[Ankur Bawiskar]

@VishakhaShi 

it's not possible directly in reporting but you can create a database view which joins certificate and business app table and then show sysId in the report created on this database view table

Example: I showed sysId of incident in OOTB incident_sla database view table

If my response helped please mark it correct and close the thread so that it benefits future readers.

[sahiltelani]

Hi @VishakhaShi,

I was going to suggest creating a DB view as Ankur did.

You can also try one more thing, by any chance, if you are allowed to create a custom field on your table, you can create a strong type field and use a script to populate the sys_id of reference record in that field. The same field will be available to be used in the Reports as well. 

Also, creating a DB view will make the fields of both the table accessible to you. If there's any requirement in the future to use some other fields of the report that will be achievable.