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
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

1

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.

John

Answered 11/03/2012 by: jverbosk
Red Belt

  • Sorry, duplicate post...
Please log in to comment

Answers

This content is currently hidden from public view.
Reason: Removed by user request
For more information, visit our FAQ's.

Answer this question or Comment on this question for clarity