get sys_id of records of a particular table in database view

[Zorawar Singh 1]

We have a requirement to show a report of All assets which are in status "In Use" but are past the "End of Life" date from the content library of the corresponding model. 

We have created a database view between the asset table and the hardware model lifecycle table ( cmdb_hardware_model_lifcycle) to achieve the same however we have noticed for some models there are multiple "End of Life" dates and for such scenarios we want to get the record where the lifecycle phase "End of Life" is recently created.

I am able to filter the records using glideAggregate but I am unable to fetch the sys_id's of the table cmdb_hardware_model_lifecycle records in the database view. Is there a way to fetch sys_ids of records only from cmdb_hardware_model_lifecycle so that the same can be passed on to the filter in a report.

[Zorawar Singh 1 · Accepted solution]

I was able to found a solution myself . I used GlideRecord() in the while loop of glideAggregate() to get the sys_ids of the record.

[Maik Skoddow]

Hi @Zorawar Singh 1 

for a database view, you always have to add additional columns if they are not already part of the view definition.

To get the Sys IDs, proceed as follows (map this to your view accordingly):

(1) click on the table name:

(2) In the related list "View Fields" click on "New":

(3) Select "Sys ID":

Maik