/build/static/layout/Breadcrumb_cap_w.png

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%'))


1 Comment   [ + ] Show comment
  • Thanks so much for your quick response. The GROUP BY did work, however, I was looking to get a separate email each time a new ticket was assigned not an email containing a list of all the "new" tickets assigned to the specific techs. I apologize but I'm new to SQL so not sure how much to remove after the GROUP_CONCAT statement to give me my results. - dfeldman 9 years ago
    • To limit to the current ticket, try adding the following to the where clause:
      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 9 years ago

Answers (1)

Answer Summary:
Posted by: grayematter 9 years ago
5th Degree Black Belt
0
To use the GROUP_CONCAT function properly, you need a GROUP BY clause.  Try adding:
GROUP BY HD_TICKET.ID
to the end of the query.  That should resolve your issue.

For details, check the documentation:  
http://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html#function_group-concat

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ