Adding Comments in Custom Email Rule results in a separate email for each comment
I've set up Custom Email Rules for New Tickets, Duplicate Tickets and Completed Tickets by copying/tweaking other peoples' SQL statements, and for the most part it works... but what I've noticed is that when I set HD_TICKET_CHANGE.COMMENT as last_comment and then include $last_comment in the email, recipients get a separate email for every comment that's ever been posted for that ticket. All I want is for them to see the most recent comment (we tend to update there, rather than in the Resolution field). It's pretty obnoxious for people to receive multiple emails each time I close their request, etc. Is there a way to just post the last comment made? I thought about playing with HD_TICKET_CHANGE_FIELD.FIELD_CHANGED = "Comment" then inputting HD_TICKET_CHANGE_FIELD.AFTER_VALUE, but I'm not sure how to make that work or even if it's the cleanest way to do what I want. Any suggestions?
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
HD_CATEGORY.NAME as category_name,
HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
HD_TICKET.ID as ticketnum,
HD_TICKET.TITLE as ticket_title,
HD_TICKET_CHANGE.COMMENT as last_comment,
HD_TICKET.CC_LIST as tick_cclist,
HD_CATEGORY.CC_LIST AS cat_cclist,
U1.EMAIL as owner_email,
HD_TICKET.CUSTOM_FIELD_VALUE1 as ticket_custom_2_value,
U2.EMAIL as submitter_email,
U2.USER_NAME as submitter_name,
concat_ws(',', U1.EMAIL, U2.EMAIL, HD_CATEGORY.CC_LIST, HD_TICKET.CC_LIST) as recipients,
concat('http://support.acboe.org/userui/ticket.php?ID=', HD_TICKET.ID) as ticket_url,
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
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.APPROVER_ID
LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_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_STATUS.NAME != 'Duplicate') and ( HD_STATUS.STATE = 'Closed') and HD_TICKET.HD_QUEUE_ID = 1 )
This is an automated email. If you reply to this email the ticket may be reopened.
This Support Ticket has been closed due to one or more of the following reasons.
1) We believe the issue has been resolved.
2) There is already a request regarding this matter in the system for you.
3) It has been a while since we last heard from you.
If you feel this case should be reopened or you have any questions regarding this ticket, please reply to this email. Your ticket will be automatically reopened and your concern addressed.
I guess this ticket rule should fire on a schedule, right?
Try adding this to the end of your statement, an extension to the WHERE section:
And HD_TICKET_CHANGE.ID = (Select Max(hdtc.ID) From HD_TICKET_CHANGE hdtc Where hdtc.COMMENT <> '' And hdtc.HD_TICKET_ID = HD_TICKET.ID)
This should limit the output to the last change event that had a non-empty comment field.