I want to send an e-mail to the customer when we manually enter a ticket.  I'm on version 6.4 - can you help.   SQL is not something I do well. 
1 Comment   [ + ] Show Comment

Comments

  • I get a Syntax error. I posted it here.
Please log in to comment

Community Chosen Answer

1

Here is the select statement we use for new tickets:

select HD_TICKET.ID, 
HD_TICKET.ID as TICKNUM, 
HD_TICKET.TITLE, 
U1.USER_NAME as OWNER_NAME, 
U3.USER_NAME as LASTINPUTNAME,  
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, 
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED, 
HD_STATUS.NAME AS STATUS_NAME, 
HD_STATUS.ORDINAL as STATUS_ORDINAL, 
STATE, 
U1.FULL_NAME as OWNER_FULLNAME, 
U1.EMAIL as OWNER_EMAIL, 
U2.USER_NAME as SUBMITTER_NAME, 
U2.FULL_NAME as SUBMITTER_FULLNAME, 
U2.EMAIL as SUBMITTER_EMAIL, 
U3.EMAIL as UPDATEREMAIL, 
U3.FULL_NAME as UPDATER_NAME,
UNIX_TIMESTAMP(HD_TICKET_CHANGE.TIMESTAMP),
HD_TICKET_CHANGE.COMMENT,
HD_CATEGORY.CC_LIST AS NEWTICKETEMAIL,
HD_CATEGORY.NAME AS CATEGORY_NAME,
U2.LOCATION AS SUBMITTER_LOCATION,
U2.WORK_PHONE AS SUBMITTER_WORK_PHONE,
HD_PRIORITY.NAME AS TICKET_PRIORITY,
HD_QUEUE.NAME AS QUEUE_NAME
from ( HD_TICKET, 
HD_PRIORITY, 
HD_STATUS, 
HD_IMPACT, 
HD_CATEGORY)
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID 
 and HD_TICKET_CHANGE.ID=<CHANGE_ID>
left join USER U1 on U1.ID = HD_TICKET.OWNER_ID
left join USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID 
left join USER U3 on U3.ID = HD_TICKET_CHANGE.USER_ID 
left join HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID  and 
HD_STATUS.ID = HD_STATUS_ID  and 
HD_IMPACT.ID = HD_IMPACT_ID  and 
HD_CATEGORY.ID = HD_CATEGORY_ID  and
HD_TICKET_CHANGE.DESCRIPTION LIKE '%Ticket Created%' and
HD_TICKET.OWNER_ID != HD_TICKET.SUBMITTER_ID 

When creating your rule use SUBMITTER_EMAIL as the column containing the email address for the Email each recipient the query results settings.


Answered 05/03/2016 by: chucksteel
Red Belt

Please log in to comment

Answers

0
Thanks that is Awesome, but I'm very lost I don't see a Submitter_email What am I missing. 
Answered 05/03/2016 by: anaccarato
Orange Belt

  • It's one of the columns being selected:
    U2.EMAIL as SUBMITTER_EMAIL,
    • Can I customize the e-mail that is sent
      • Yes, when creating the rule check the box for Email each recipient in query results. For Column containing email addresses: use SUBMITTER_EMAIL. Fill in the subject and message boxes appropriately. Any of the columns selected can be used as variables in the email. For example, the ticket title is $title, the ticket ID is $id, and the user's full name is $submitter_fullname.
Please log in to comment
This content is currently hidden from public view.
Reason: Removed by member request
For more information, visit our FAQ's.

Answer this question or Comment on this question for clarity