How can I send an email on a due date?
I have a ticket rule created for when a field with a due date matches todays date, it will email someone. The problem is that it is still sending an email on all days even if the date does not match today's date, it just returns a null value in the email.
Example Email when it matches todays date:
Email forwarding ends today for HRLMM.
Example email for any other date that doesn't match today's date:
Email forwarding ends today for .
I have it set to run once daily. I want it to send an email if and only if the date for HD_TICKET.CUSTOM_FIELD_VALUE7 matches today. Otherwise I do not want an email sent.
Here is my SQL for this rule.
HD_STATUS.NAME AS STATUS,
HD_TICKET.TITLE, -- $title
S.FULL_NAME AS SUBMITTER_NAME,
HD_CATEGORY.NAME AS CATEGORY,
DATE_FORMAT(HD_TICKET.CUSTOM_FIELD_VALUE7,'%b %d %Y') AS EMAIL_END, -- $email_end
EMPLOYEE.FULL_NAME as EMPLOYEE_FNAME, -- $employee_fname
O.FULL_NAME AS OWNER_NAME,
HD_PRIORITY.NAME AS PRIORITY,
GROUP_CONCAT(DISTINCT HD_TICKET_CHANGE.COMMENT SEPARATOR '\n') AS HD_TICKET_CHANGE_COMMENT_GROUPED,
'firstname.lastname@example.org' AS NEWTICKETEMAIL -- $newticketemail
JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID)
LEFT JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
LEFT JOIN USER EMPLOYEE ON EMPLOYEE.ID = HD_TICKET.CUSTOM_FIELD_VALUE4
(HD_TICKET.HD_QUEUE_ID = 10) AND ((DATE(HD_TICKET.CUSTOM_FIELD_VALUE7) = CURDATE()))
Email each recipient in query results
Column containing email addresses:
Message:Email forwarding ends today for $employee_fname. Thanks,
Run update query
Please log in to answer
Posted by: Hobbsy 5 years ago
Why not create a switch field, so that when the due date is reached an email is sent, the switch is switched and no more emails are sent?
First also consider that if you check the DB the field HD_TICKET.SLA_Notified is only populated when a due date is reached, so your ticket rule needs to only run if HD_TICKET.SLA_NOTIFIED is not Null.
To create a switch field, add a custom field to your ticket and set it as a single select drop-down with values of 1 and 2 and a default value of 1.
Leave the switch field as visible until your know the logic has worked, as you can then also select it in the ticket rule wizard
Next setup the ticket rule with the following logic
HD_TICKET.SLA_NOTIFIED is not NULL and
when the switch field is = 1
Create your email in the ticket rule
Set the Switch field to 2
Set the rule to run every 15 mins, and when it works copy the rule and save it to run "ON ticket save" as well.
You should now have a rule that fires when the Due date is reached and only sends a single escalation email