Scripting

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?

select HD_TICKET.*,

                        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,

                        STATE,

                        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 )

Message:

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.

Comment: $last_comment

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.

Regards,

Data Center