Hello fellow ninjas. I have reached an impasse in my knowledge. I am attempting to find a way to setup emails that will go out when a new ticket is created. These emails would go out to all members of the label group assigned to that queue. Unfortunately i have limited knowledge of sql and I am experience problems when attempting to determine what code I would need to generate this effect. All help is greatly appreciated.
Answer Summary:
0 Comments   [ - ] Hide Comments


Please log in to comment

Answer this question or Comment on this question for clarity


I've written a blog post to address this question:
Answered 10/16/2014 by: PHKace
Blue Belt

  • I set it up as described in your post but i am receiving an error on line 25 of the select statement. I know just enough about sql coding to recognize it when i see and that is about it.
    • Do you have a copy of the error?
      • 10/20/2014 08:36:53> Starting: 10/20/2014 08:36:53 10/20/2014 08:36:53> Executing Select Query... 10/20/2014 08:36:53> mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''%Ticket Created% and (HD_TICKET.ID = 2)' at line 25] in EXECUTE("select HD_TICKET.ID, HD_TICKET.ID ticket_number, -- $ticket_number HD_TICKET.TITLE ticket_title, -- $ticket_title SUBMITTER.FULL_NAME ticket_submitter_name, -- $ticket_submitter_name SUBMITTER.EMAIL ticket_submitter_email, -- $ticket_submitter_email CAT.NAME ticket_category, -- $ticket_category IMPACT.NAME ticket_impact, -- $ticket_impact PRIORITY.NAME ticket_priority, -- $ticket_priority STATUS.NAME ticket_status, -- $ticket_status group_concat(OWNERS.EMAIL) as EMAILCOLUMN from HD_TICKET join HD_TICKET_CHANGE C on HD_TICKET.ID = C.HD_TICKET_ID and C.ID = 3 left join USER SUBMITTER on HD_TICKET.SUBMITTER_ID = SUBMITTER.ID left join HD_CATEGORY CAT on HD_TICKET.HD_CATEGORY_ID = CAT.ID left join HD_IMPACT IMPACT on HD_TICKET.HD_IMPACT_ID = IMPACT.ID left join HD_PRIORITY PRIORITY on HD_TICKET.HD_PRIORITY_ID = PRIORITY.ID left join HD_STATUS STATUS on HD_TICKET.HD_STATUS_ID = STATUS.ID join HD_QUEUE_OWNER_LABEL_JT HDQOLJT on HD_TICKET.HD_QUEUE_ID = HDQOLJT.HD_QUEUE_ID join USER_LABEL_JT ULJT on HDQOLJT.LABEL_ID = ULJT.LABEL_ID join USER OWNERS on ULJT.USER_ID = OWNERS.ID where C.DESCRIPTION like '%Ticket Created% and (HD_TICKET.ID = 2) ")
      • Looks like you may be missing the single quote after '%Ticket Created%
      • awesome that fixed it. Thank you for all of your help.
Please log in to comment