We are setting up email layout for open tickets and for closed tickets. We have custom fileds that we use under ticket layout called Custom_2 and Custom_1. How do I reference them in tickets. I believe it should be something similiar to $ticket_custom_1 But any help would be greatly apprecaited. Also is there a list of variables because only a few default show on the right of the screen.

Such as $userui_url, $helpdesk_name, $helpdesk_email, $ticket_escalation_minutes, $ticket_priority, $ticket_number, $ticket_title, $ticket_url, $ticket_history, $ticket_submitter_name, $ticket_submitter_email, $ticket_owner_name, $ticket_owner_email, $change_desc

Is there a bigger list of commands on the web somewhere or am I not looking through the manual in the correct location?
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
I don't think you can do that with the built in rules (I haven't seen it anyway).

You could definitely do it with a custom helpdesk rule.

You would be using the send an email for each result row.

Here's the help.
This rule assumes that the Select Query will return a column that will contain an email address, and that you want to send a mail to that address. Basically, for each resulting row from the 'Select Query' statement, the text in the 'Email Body' will be sent to the recipients from the field 'Email Column'.

Variables will be replaced in the subject and body of the email. Strings like $title and $due_date will be replaced by the values in the columns names TITLE and DUE_DATE respectively. Lowercase with a dollar sign on the front. Any column returned by the select statement can be replaced in that way.

The SQL generated by the Ticket Rule Wizard will supply OWNER_EMAIL and SUBMITTER_EMAIL as well as CC_LIST as possible values.
Answered 12/30/2011 by: dchristian
Red Belt

Please log in to comment
0
Thanks
Answered 12/30/2011 by: quickwhips
Orange Belt

Please log in to comment
0
I don't think you can do that with the built in rules (I haven't seen it anyway). What David means is that you cannot do that with the built-in email on events templates. And that is very true. The only fields you can use in those templates are listed in the R.H.S. help blurb. In 5.4 we are adding many more variables to that including custom fields.

If you want those fields you will have to turn off the email on events and rewrite those events as ticket rules and write the query in the way that David illuminates. Rewriting them as rules is non-trivial.

But I was bored this morning :D so here is a rewrite for all of the "change" fields. Here is how to use it:
Note: this only replaces the "change" emails which are driven by the template "Email Ticket Creation Acknowledgement"
  1. Do not change any of your email on events checkboxes. This rule relies on those checkboxes to know who to email
  2. In contrast to #1 the email text for the template "Email Ticket Creation Acknowledgement" is no longer relevant, but if you like the content you can reuse it ...keep reading
  3. Create a new blank rule
  4. Title it "Replacing Email Ticket Creation Acknowledgement"
  5. Change frequency to "on ticket save"
  6. Change select query to select query below. If needed change the hostname (currently it is "k1000")
  7. Check the box for "send an email for each result row"
  8. --> set subject the same as what you had in the template
  9. --> set email column as NOTIFY_USER
  10. --->set Email Body to same as what you had in the template
  11. Change update query to update query below
  12. NEVER HIT RUN NOW ON THIS RULE (although that should fail if you do)
  13. save it

Features:
  • replaces 80% of the checkboxes (all the change driven ones).
  • will give the proper url to admin portal users and user portal users
  • leverages your existing checkboxes so you can change them whenever you want
  • uses column names that match the email template variables so you can copy/ paste the email you used before
  • you can add additional values in by modifying the select list
  • will not notify anyone that has an invalid email address including people on cc list
  • owners only comments and owners owners field changes will go to owners only
  • ticket history is only the history that users are allowed to see.
  • change emails will go out immediately with no delay (normally they are on a 3 minute interval)
select query:select C.ID,
HD_TICKET.ID TICKET_NUMBER,
NOTIFIERS.EMAIL NOTIFY_USER,
P.NAME TICKET_PRIORITY,
HD_TICKET.TITLE TICKET_TITLE,
case when PV.VALUE<>'HIDE' then CONCAT('http://',KBOX.HOST,'/adminui/ticket.php?ID=',HD_TICKET.ID)
else CONCAT('http://',KBOX.HOST,'/adminui/ticket.php?ID=',HD_TICKET.ID) end TICKET_URL,

IFNULL(GROUP_CONCAT(DISTINCT CONCAT('----- Change by ', UPDATER.EMAIL,if(H.VIA_EMAIL=',',' (via email)'),' at ',cast(H.TIMESTAMP as char),' -----\n',
H.DESCRIPTION,'\n',H.COMMENT)
ORDER BY H.ID DESC SEPARATOR '\n'),'no user entries') TICKET_HISTORY,
P.ESCALATION_MINUTES TICKET_ESCALATION_MINUTES,

IFNULL(SUBMITTER.FULL_NAME,'unassigned') TICKET_SUBMITTER_NAME,
IFNULL(OWNER.FULL_NAME,'unassigend') TICKET_OWNER_NAME,
IFNULL(SUBMITTER.EMAIL,'unassigned') TICKET_SUBMITTER_EMAIL,
IFNULL(OWNER.EMAIL,'unassigend') TICKET_OWNER_EMAIL,

case when NOTIFIERS.ID=OWNER.ID then CONCAT(UPDATER.EMAIL,if(C.VIA_EMAIL=',',' (via email)'),' at ',cast(C.TIMESTAMP as char),'-----\n',
C.DESCRIPTION,'\n',C.OWNERS_ONLY_DESCRIPTION,'\n',C.COMMENT)
else CONCAT(UPDATER.EMAIL,if(C.VIA_EMAIL=',',' (via email)'),' at ',cast(C.TIMESTAMP as char),'-----\n',
C.DESCRIPTION,'\n',C.COMMENT) end CHANGE_DESC /* future goal: modify the owners to be based on owner label */

from HD_TICKET
JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.ID=<CHANGE_ID>
left JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID=HD_TICKET.ID and H.OWNERS_ONLY=0
JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
JOIN USER NOTIFIERS ON
/* notifiers is in the list */
(FIND_IN_SET(NOTIFIERS.ID,C.NOTIFY_USERS)>0 and
CONCAT(NOTIFIERS.EMAIL,',')
RLIKE '^([[:alnum:][.period.][.hyphen.][.underscore.]]+@([[:alnum:][.hyphen.][.underscore.]]+[.period.])+([[:alnum:][.hyphen.]]){2,4}[[.comma.]]+[[:space:]]*)+$' )
OR
/* email is in list and it's a valid address */
(FIND_IN_SET(NOTIFIERS.EMAIL,C.NOTIFY_USERS)>0 and
CONCAT(NOTIFIERS.EMAIL,',')
RLIKE '^([[:alnum:][.period.][.hyphen.][.underscore.]]+@([[:alnum:][.hyphen.][.underscore.]]+[.period.])+([[:alnum:][.hyphen.]]){2,4}[[.comma.]]+[[:space:]]*)+$' )
JOIN USER_ROLE_PERMISSION_VALUE PV ON PV.ROLE_ID=NOTIFIERS.ROLE_ID and PV.PERMISSION_ID=43
JOIN (select 'k1000' -- < change this value to your server name
HOST) KBOX ON 1=1
LEFT JOIN USER OWNER ON OWNER.ID=OWNER_ID
LEFT JOIN USER SUBMITTER ON SUBMITTER.ID=SUBMITTER_ID and C.OWNERS_ONLY=0
LEFT JOIN USER UPDATER ON C.USER_ID=UPDATER.ID

GROUP BY HD_TICKET.ID, NOTIFIERS.ID
HAVING 1=1

update query:UPDATE HD_TICKET_CHANGE C
SET MAILED=1,
MAILED_TIMESTAMP=NOW()
WHERE
C.ID IN (<TICKET_IDS>)
Answered 12/30/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0

I know this is an old post, but did you ever find what you wanted?  My suggestion would be to try the fields listed in the email on event page.  There is one for the custom fields there that we use:  ("x" being the custom field number), but I haven't tried them in the ticket itself.

$ticket_custom_X_label, $ticket_custom_X_value

Answered 03/07/2014 by: wmwooten
White Belt

Please log in to comment
Answer this question or Comment on this question for clarity