/bundles/itninjaweb/img/Breadcrumb_cap_w.png
Hi,

I am looking for a  query to send an email to the submitter when the owner change the status to "Need more information". I am not a SQL-expert, so I hope someone can help me.

I saw I question before about this subject. But could someone expain it to me?

Has anyone fully query for me?

Thanks.

Rodin
0 Comments   [ - ] Hide Comments

Comments

Please log in to comment

Answer this question or Comment on this question for clarity

Answers

0
You can do something like this...

select distinct(HD_TICKET.ID), U.EMAIL as EMAIL from HD_TICKET
left join HD_TICKET_CHANGE TC on TC.HD_TICKET_ID = HD_TICKET.ID
left join HD_TICKET_CHANGE_FIELD CF on CF.HD_TICKET_CHANGE_ID = TC.ID
left join USER U on U.ID = HD_TICKET.SUBMITTER_ID
where CF.AFTER_VALUE = 'Need more information'
and CF.FIELD_CHANGED = 'STATUS_NAME'
and TC.TIMESTAMP > subdate(NOW(), INTERVAL 10 SECOND)
and HD_TICKET.HD_QUEUE_ID = 1

This will pull the ID and Submitter email from tickets that have been changed in the last 10 seconds to a status of Need more information from Queue 1.

Column containing email address: EMAIL

You can use the ID number in the subject or body of the email by using $id

Select 'On Ticket Save' as the schedule
Answered 10/27/2014 by: h2opolo25
Red Belt

  • KACE provides a <CHANGE_ID> variable that you can use when crafting queries like this which allows you to join to the HD_TICKET_CHANGE table based on the change which was triggered when the ticket was saved. Using that variable the query would look like this:

    select HD_TICKET.ID, U.EMAIL as EMAIL from HD_TICKET
    left join HD_TICKET_CHANGE TC on TC.HD_TICKET_ID = HD_TICKET.ID and TC.ID = <CHANGE_ID>
    left join HD_TICKET_CHANGE_FIELD CF on CF.HD_TICKET_CHANGE_ID = TC.ID
    left join USER U on U.ID = HD_TICKET.SUBMITTER_ID
    where CF.AFTER_VALUE = 'Need more information'
    and CF.FIELD_CHANGED = 'STATUS_NAME'
    and HD_TICKET.HD_QUEUE_ID = 1

    Because we are using the <CHANGE_ID> variable we don't have to limit to changes that occurred only in the past 10 seconds.
Please log in to comment