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.
Thanks,
Example email for any other date that doesn't match today's date:
Email forwarding ends today for .
Thanks,
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.
SELECT
HD_STATUS.NAME AS STATUS,
HD_TICKET.ID,
HD_TICKET.TITLE, -- $title
S.FULL_NAME AS SUBMITTER_NAME,
HD_CATEGORY.NAME AS CATEGORY,
HD_TICKET.CREATED,
DATE_FORMAT(HD_TICKET.CUSTOM_FIELD_VALUE7,'%b %d %Y') AS EMAIL_END, -- $email_end
EMPLOYEE.FULL_NAME as EMPLOYEE_FNAME, -- $employee_fname
HD_TICKET.DUE_DATE,
O.FULL_NAME AS OWNER_NAME,
HD_TICKET.PARENT_ID,
HD_PRIORITY.NAME AS PRIORITY,
GROUP_CONCAT(DISTINCT HD_TICKET_CHANGE.COMMENT SEPARATOR '\n') AS HD_TICKET_CHANGE_COMMENT_GROUPED,
'user@abc.com' AS NEWTICKETEMAIL -- $newticketemail
FROM HD_TICKET
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
WHERE
(HD_TICKET.HD_QUEUE_ID = 10) AND ((DATE(HD_TICKET.CUSTOM_FIELD_VALUE7) = CURDATE()))
----------------------------------------------------------
Email each recipient in query results
Subject:
Column containing email addresses:
Message:Email forwarding ends today for $employee_fname.
Thanks,
Run update query
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
Hobbsy
8 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
Select when
HD_TICKET.SLA_NOTIFIED is not NULL and
when the switch field is = 1
Create your email in the ticket rule
Update
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
Comments:
-
How do I trigger the HD_TICKET.SLA_NOTIFIED when I am using the date in HD_TICKET.CUSTOM_FIELD_VALUE7? Would I be better off using the built-in Due_Date field instead? - totero21 8 years ago
-
yes, setup the SLA config and then the Due date will be used as your target SLA Date - Hobbsy 8 years ago
-
Ok, the problem we have is that we do not like the format of the Due_Date field. Right now we use the custom date field that brings up the calendar. Is there another way to do this without using the due_date field? - totero21 8 years ago
