Is there a way to include the Custom_Field_Values in an e-mail?

Query
select HD_TICKET.*, DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
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_PRIORITY.ORDINAL as PRIORITY_NUMBER,
HD_TICKET.CUSTOM_FIELD_VALUE1 as C1,
'newticket@<domain>.com' AS NEWTICKETEMAIL,
HD_TICKET.ID AS TICKNUM,
CAT.NAME AS CATEGORY,
HD_TICKET.TITLE,
C.COMMENT,
I.NAME AS IMPACT,
P.NAME AS PRIORITY,
STATE,
if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
case upper(STATE)
when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
U1.CUSTOM_4 as OoO,
if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', ')))) as APPROVAL_STATUS,
Q.NAME as QUEUE_NAME
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
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
JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_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 U1.CUSTOM_4 = 'out of office'
and HD_TICKET.HD_QUEUE_ID = 5


Email
$owner_fullname is out of the office and was auto assigned an incoming ticket by $submitter_fullname. The ticket has been moved to an unassigned owner status and you have been notified due to being an owner of the IT queue. You can view this ticket online at http://k1000/userui/ticket.php?ID=$ticknum

The ticket details are:
Ticket: $ticknum
Category: $category
Title: $title
Impact: $impact
User Suggested Priority: $C1
Submitter: $submitter_fullname ($submitter_email)
Status: $status_name
Comment: $comment


For whatever reason that I'm not aware of it's not referencing the Custom_field_value1 even though i have the "HD_TICKET.CUSTOM_FIELD_VALUE1 as C1" statement, which should allow you to do the $C1, like what you can do with e-mailing on a new ticket from Send e-mail on ticket creation.

Thanks
Answer Summary:
If that is CUSTOM_1 in the ticket layout then use HD_TICKET.CUSTOM_FIELD_VALUE0 in the query
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

1
If that is CUSTOM_1 in the ticket layout then use HD_TICKET.CUSTOM_FIELD_VALUE0 in the query
Answered 01/06/2012 by: GillySpy
Seventh Degree Black Belt

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
Sorry, it's actually Custom_2 in the management screen, CUSTOM_FIELD_VALUE1 in the database
Answered 01/06/2012 by: natearms
Senior Yellow Belt

Please log in to comment
0
Oh, i see you've got it upper case in the email where they have to be lower case. i.e. $c1

But I didn't try it. I avoid using such small tokens as it's ambiguous and with the number it's easy to have this overlap. e.g. $c1 and $c10 would be a problem.
Answered 01/06/2012 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
That was it, thanks! I also changed it from C1 to something without a number.
Answered 01/06/2012 by: natearms
Senior Yellow Belt

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