/build/static/layout/Breadcrumb_cap_w.png
10/24/2018 157 views
Hello, looking to see if someone can assist with the query below. Trying to send a reminder email after a certain number of days if survey is not filled. 

Select SQL:
SELECT DISTINCT HD_TICKET.ID
  ,HD_TICKET.ID AS TICKET_ID # $TICKET_ID
  ,HD_TICKET.TITLE AS TICKET_TITLE # $TICKET_TITLE
  ,HD_STATUS.NAME AS STATUS_NAME
  ,HD_STATUS.STATE AS STATE
  ,SUBMITTER.FULL_NAME AS SUBMITTER_FULLNAME
  ,SUBMITTER.EMAIL AS SUBMITTER_EMAIL
  ,HD_TICKET.TIME_CLOSED
  ,HD_TICKET.SATISFACTION_RATING AS RATING
  ,HD_TICKET.SATISFACTION_COMMENT AS COMMENT
 
FROM (HD_TICKET, HD_STATUS)
LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
        
WHERE HD_STATUS.ID = HD_TICKET.HD_STATUS_ID
  AND HD_STATUS.STATE = 'closed' -- ticket is still closed
  AND -- no survey rating or comment
  (SATISFACTION_COMMENT IS NULL
  OR SATISFACTION_COMMENT LIKE ''
  OR SATISFACTION_RATING IS NULL
  OR SATISFACTION_RATING LIKE ''
 )
  AND HD_TICKET.HD_QUEUE_ID = 20 -- insert your queue id here
  AND DATE(TIME_CLOSED) = DATE(NOW())-1 -- closed 90 days ago

When I call the variables $TICKET_ID and $TICKET_TITLE under "Email each recipient in query results" the email gets sent out correctly but the variables do not give the values and only show up as variables in the email that is sent out
Answer Summary:
1 Comment   [ + ] Show comment

Comments


Answer Chosen by the Author

2
Make sure in your email template you have the variables in lower case
Answered 10/25/2018 by: Hobbsy
Red Belt