Add comment to parent when child is closed
We are currently using a 2 queue design. 1 queue is for work, the other is for change approvals. Our process is to create a work ticket, and then if a change approval is required, we create a child ticket that goes into the change request queue. in the change request queue, the closed states are "change approved", or "change denied".
Currently the only way users know if their change has been approved is to go in periodically and check the work ticket to see if the child ticket has been closed. I would like to be able to use the SQL to create a ticket rule that automatically adds a comment to the parent ticket that says your "change requested "ticket ID" has been denied, or approved" upon the close of the child ticket.
Has anyone seen any code for this?
Answer Chosen by the Author
Yes, this is possible. A select statement like this will return the ID of the parent ticket:
select HD_TICKET.ID AS CHILDTICKETID,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
Q.NAME as QUEUE_NAME,
PTICK.ID as ID
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
JOIN HD_TICKET_CHANGE_FIELD ON HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID=<CHANGE_ID>
LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN HD_TICKET PTICK on PTICK.ID = HD_TICKET.PARENT_ID
where HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID
and HD_STATUS.ID = HD_TICKET.HD_STATUS_ID
and HD_IMPACT.ID = HD_TICKET.HD_IMPACT_ID
and HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID
and HD_TICKET_CHANGE_FIELD.AFTER_VALUE = 'change approved'
This will match tickets where the status has changed to 'change approved' when saved. You can use the functionality to append a comment to the ticket. You would need separate rules for 'change approved' and 'change denied'. Using these rules you could also update the status of the parent ticket or a custom field to indicate that the approval status has changed.
Answered 03/23/2016 by: chucksteel
Please log in to comment
log in to commentPlease