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

Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • 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.
    • 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.
Please log in to comment

Answers

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

Answered 07/03/2014 by: grayematter
Fourth Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity