$custom_field_value1X doesnt works
Hello togehter,
if i add a ticket-rule which send a mail with the var $custom_field_value11, 12 ,13 etc. it doesnt work.
For example:
the value in custom_1 is PHONE
the value in custom_12 is APPLIKATION
If i send a mail which contains:
The $custom_field_value_12 is requiered.
It should send : The APPLIKATION is requiered.
It send : The PHONE2 i requiered.
The variable cuts after the 1.
Thanks a lot for help.
Answers (3)
Two things:
Does your SQL query select statement include the custom fields? You need to make sure that you there is a line that includes the custom_field_value1, etc. for them to be available as variables in the email.
The column names in the table are zero based, so custom field 1 is stored in custom_field_value0 and field 12 is stored in custom_field_value11. Be sure you are selecting the appropriate column in your select statement.
If you aren't sure about the SQL statement please post it here and we can check it for you.
Comments:
-
Yes the collums has the right value because my subject for mail contains CUSTOM_FIELD_VALUE13. The Email was send correctly. But If i add the $CUSTOM_FIELD_VALUE13 or CUSTOM_14 or CUSTOM_FIELD_VALUE13 or HD_TICKET.CUSTOM_FIELD_VALUE14 or 13 no information in the new Ticket. - Johnzko 10 years ago
-
Can you post your SQL query? - chucksteel 10 years ago
select HD_TICKET.*,
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,
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,
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
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 (((( HD_TICKET.CUSTOM_FIELD_VALUE15 = '1') AND HD_TICKET.CUSTOM_FIELD_VALUE18 != 'YES') and HD_TICKET.HD_QUEUE_ID = 5 ) and HD_TICKET.APPROVAL = 'approved')
Comments:
-
You are selectin HD_TICKET.* so all of the custom field values should be accessible. I'm not sure if the variables are case sensitive but I normally put them in all lower case. So my email message uses $custom_field_value0, $custom_field_value11, etc. - chucksteel 10 years ago
-
Ok. What should i do ? - Johnzko 10 years ago
-
In your original post you have an underscore between value and the number ($custom_field_value_12 instead of $custom_field_value12). Is it correct on your KBOX? If that is correct then you might need to submit a ticket with technical support. - chucksteel 10 years ago
-
Oh sorry. In my KBox its right. $custom_field_value1 is ok. only by 11, 12 ,13 ,14 etc it doesnt works - Johnzko 10 years ago
-
@chucksteel Have you a Hardware-KACE or a VM-KACE ? - Johnzko 10 years ago
-
We have an appliance. One thing I thought of this morning. Have you tried explicitly selecting one of the custom fields and assigning an alias? So add CUSTOM_FIELD_VALUE12 as APPLICATION and then use $application in the email? - chucksteel 10 years ago
-
Than you very much. I had called with the KACE-Support. The Engineer had told me exactly your command. Now it works fine. (= - Johnzko 10 years ago