/build/static/layout/Breadcrumb_cap_w.png

K1000 Ticket Rule - Email Approver

This bare-bones ticket rule will send an email to the Approver as soon as the Approver is set and the ticket is saved. 

Title:

Email Approver
Notes: Send an email to the Approver as soon as the Approver is set and the ticket is saved.
Frequency: on Ticket Save
Select Query: 
select
  HD_TICKET.ID as TICKNUM,
  HD_TICKET.TITLE as TITLE,
  APPROVER.EMAIL as EMAILCOLUMN
from
  HD_TICKET join HD_TICKET_CHANGE C on HD_TICKET.ID = C.HD_TICKET_ID
    and C.ID = <CHANGE_ID>
  join USER APPROVER on HD_TICKET.APPROVER_ID = APPROVER.ID
where
  (C.DESCRIPTION like '%Changed ticket Approver from%')
 or (C.DESCRIPTION like '%Ticket Created%'
 and APPROVER_ID != 0) 
 √  Send an email for each result row
Subject:

 Ticket [TICK:$ticknum] Requires Your Approval 
Email Column:

EMAILCOLUMN
Email Body:

Ticket: $ticknum - "$title" requires your approval.
Please open this ticket at:
http://kbox/adminui/ticket.php?ID=$ticknum

 

Posted 11 years ago 6027 views

Comments

  • What version of K1000 does this work on? Trying it on 5.4SP1 with errors. - James_Rico 11 years ago
    • Hi James. This will work on 5.4SP1. One issue was it did not work if the Approver was set on ticket creation. I've updated the query and verified it works on ticket creation now as well. - PHKace 11 years ago
      • I am confused as to where the <CHANGE_ID> comes from. I know that I can plug in a value for existing database records and get this to work but how does the query pick up the value. I get an error when I use <CHANGE_ID> in the query. Can you shed some light? - marceauk 11 years ago
      • Whenever an On Ticket Save rule runs, a CHANGE_ID is associated with any change that happened. When a ticket rule runs, the Kbox replaces <CHANGE_ID> with the ID of the change in the HD_TICKET_CHANGE table. Using a 3rd party tool such as MySQL Workbench, or using the Run Now button on a ticket rule will give an error because it doesn't replace <CHANGE_ID> with a real number. - PHKace 11 years ago
  • it works when use with parentheses (<CHANGE_ID>) on 5.5. - Aaron Cool 11 years ago
  • I started off with this code and modified it to work a little better (for me), because for some reason, the part about the description wasn't working. This is on 6.3, by the way. I'm new to SQL, so it may be a little messy.
    The difference is that this will continue to email the approver each time the ticket is saved until the approval is set (That is, not "none" or blank).

    ===================
    select
    HD_TICKET.ID as TICKNUM,
    HD_TICKET.TITLE as TITLE,
    APPROVER.EMAIL as EMAILCOLUMN,
    SUBMITTER.FULL_NAME AS SUBMITTER_FNAME
    from
    HD_TICKET join HD_TICKET_CHANGE C on HD_TICKET.ID = C.HD_TICKET_ID
    and C.ID = (<CHANGE_ID>)
    join USER APPROVER on HD_TICKET.APPROVER_ID = APPROVER.ID
    JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID

    where
    C.ID = (<CHANGE_ID>)
    and APPROVER_ID != 0
    and (APPROVAL = 'none' or APPROVAL = '')
    ===================

    This also allowed me to add the submitter's name to the email:
    ===================
    Ticket: $ticknum - "$title," submitted by $submitter_fname, requires your approval.
    ===================
    Hope this can help someone. - ondrar 9 years ago
  • Does anyone have an update for this rule. I tried this and the select isnt picking up anything. Im trying this in flyspeed but clearly dont know what Im doing. I dont under stand the "C" in this statement.. has an update caused this not to work?

    "HD_TICKET join HD_TICKET_CHANGE C on HD_TICKET.ID = C.HD_TICKET_ID
    and C.ID = (<CHANGE_ID>)" - barchetta 3 years ago
This post is locked
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ