How to send email to supervisor when specific technicians are assigned a work ticket?
I'm trying to set up a custom rule to email a supervisor when specific technicians are assigned a ticket. The problem I am running into is that it emails the supervisor when anyone is assigned a ticket in the queue. I have the rule to execute when the ticket is saved. Below is the sql code I'm using. Any suggestions would be greatly appreciated. When I plug this into a report, it pulls correctly. Thanks.
SELECT HD_TICKET.ID,S.FULL_NAME AS SUBMITTER_NAME,O.FULL_NAME AS OWNER_NAME,HD_STATUS.NAME AS STATUS,HD_TICKET.CREATED,HD_TICKET.DUE_DATE,HD_CATEGORY.NAME AS CATEGORY,GROUP_CONCAT(DISTINCT HD_TICKET_CHANGE.COMMENT SEPARATOR '\n') AS HD_TICKET_CHANGE_COMMENT_GROUPED FROM HD_TICKET LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) LEFT JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID) WHERE (HD_TICKET.HD_QUEUE_ID = 3) AND (HD_STATUS.NAME = 'New') AND ((O.FULL_NAME like '%Feldman%') OR (O.FULL_NAME like '%Partrain%'))
Answers (1)
and HD_TICKET.ID = ( select TC.HD_TICKET_ID from HD_TICKET_CHANGE TC where TC.ID = <CHANGE_ID>) )
This will not work in a query editor, but it will work in the ticket rule. To test, replace <CHANGE_ID> with a valid change_id. - grayematter 10 years ago