K1000 Reporting Using Custom Ticket Fields
I am trying to create a report that outlines tickets created and closed over the past seven days - this part I have....The issue is one of the fields that I want included in the report is a custom field (Custom_1) which is a location field. On the report the column shows, but for each line the location is blank. This is a required field so I know that the field is populated.
Here is my SQL
SELECT O.FULL_NAME AS OWNER_NAME,HD_PRIORITY.NAME AS PRIORITY,HD_STATUS.NAME AS STATUS,HD_TICKET.CUSTOM_FIELD_VALUE1 FROM HD_TICKET LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 2) AND ((TIMESTAMP(HD_TICKET.CREATED) <= NOW() AND TIMESTAMP(HD_TICKET.CREATED) > DATE_SUB(NOW(),INTERVAL 7 DAY))) AND ((TIMESTAMP(HD_TICKET.TIME_CLOSED) <= NOW() AND TIMESTAMP(HD_TICKET.TIME_CLOSED) > DATE_SUB(NOW(),INTERVAL 7 DAY))) ORDER BY OWNER_NAME
Community Chosen Answer
If the custom field is Custom_1 in the GUI, the corresponding value for it in SQL will be HD_TICKET.CUSTOM_FIELD_VALUE0 - just keep in mind it starts counting from 0 (and will therefore be one number lower) when referencing custom fields in your queries.