Scripting

This would need to be a ticket rule not a script. Here's the one I have to send an email on owner assignment:

SELECT

-- ticket fields
HD_TICKET.ID, -- $id
HD_TICKET.ID AS TICKNUM, -- $ticknum
HD_TICKET.TITLE, -- $title
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified

-- change fields
C.COMMENT, -- $comment
C.DESCRIPTION, -- $description
GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://kbox.wacoisd.org/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history


-- about the updater
UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
UPDATER.EMAIL AS UPDATER_EMAIL, -- $updater_email
IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional

-- about the owner
OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
OWNER.EMAIL AS OWNER_EMAIL, -- $owner_email
IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user

-- about the submitter
SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
SUBMITTER.EMAIL AS SUBMITTER_EMAIL, -- $submitter_email
-- about priority
P.NAME AS PRIORITY, -- $priority

-- about status
S.NAME AS STATUS, -- $status

-- about impact
I.NAME AS IMPACT, -- $impact

-- about category
CAT.NAME AS CATEGORY, -- $category

-- other fields
HD_TICKET.CUSTOM_FIELD_VALUE0 AS LOCATION, -- $location
HD_TICKET.CUSTOM_FIELD_VALUE1 AS ROOM, -- $room
HD_TICKET.CUSTOM_FIELD_VALUE2 AS WISD_NUM, -- $wisdnum
HD_TICKET.CUSTOM_FIELD_VALUE7 AS EQUIPMENT, -- $equipment
HD_TICKET.CUSTOM_FIELD_VALUE5 AS DESCRIPTION, -- $description
HD_TICKET.CUSTOM_FIELD_VALUE6 AS USER_EMAIL, -- $user_email


FROM HD_TICKET
/* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
/*AND C.ID=<CHANGE_ID>*/
/* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
/* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
/* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
/* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
/* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
/* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
/* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
/* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
WHERE
C.DESCRIPTION LIKE 'Changed ticket Owner%'

/* this is necessary when using group by functions */
GROUP BY HD_TICKET.ID
HAVING 1=1

I have some custom fields in there that we have set for our purposes. It should illustrate how to set your own custom fields.

Then I check the box next to send an email for each row and use the variables set in the select query in the email:

A Ticket has been assigned to you.

Ticket information:
Ticket: $ticknum
From: $submitter_fname ($submitter_email)

Location: $location
Room: $room
Equipment: $equipment
Service Tag/WISD#: $wisd_num
Description: $description

Opening Comment: $comment - lmland 11 years ago

Upon further reflection and working on something unrelated I realized that this will send you an email every time the work order is saved, because after the first time it is assigned to someone it the field C.DESCRIPTION will always match the criteria. Working on a solution. I will add it when i figure it out. - lmland 11 years ago

The rule could only email the first time a ticket is assign to someone by changing

C.DESCRIPTION LIKE 'Changed ticket Owner%'

to

C.DESCRIPTION LIKE 'Changed ticket Owner from"Unassigned" to%'

Still working on one that will catch any owner change. - lmland 11 years ago

Email on events for THIS particular event seems most reasonable, (and trust me, I use ticket rules for almost every other email). You can click on CUSTOMIZE EMAILS to customize the physical email sent out.


I have rules for emails for about every event but that one + ticket creation. - Wildwolfay 11 years ago