Custom_Field_Values and emails
Is there a way to include the Custom_Field_Values in an e-mail?
Query
Email
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
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$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: $commentFor 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
0 Comments
[ + ] Show comments
Answers (4)
Answer Summary:
If that is CUSTOM_1 in the ticket layout then use HD_TICKET.CUSTOM_FIELD_VALUE0 in the query
If that is CUSTOM_1 in the ticket layout then use HD_TICKET.CUSTOM_FIELD_VALUE0 in the query
Please log in to answer
Posted by:
GillySpy
12 years ago
If that is CUSTOM_1 in the ticket layout then use HD_TICKET.CUSTOM_FIELD_VALUE0 in the query
Posted by:
natearms
12 years ago
That was it, thanks! I also changed it from C1 to something without a number.
Posted by:
natearms
12 years ago
Sorry, it's actually Custom_2 in the management screen, CUSTOM_FIELD_VALUE1 in the database
Posted by:
GillySpy
12 years ago
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.
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.
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.