Can you automatically assign an approver upon ticket creation?
We are creating a ticket that multiple users can access to update, however, we want to set the manager to a specific person. We want to avoid having to do this manually every time a new ticket is created, and we would like to make the field read only, so none of the other users can modify it. I have tried creating a ticket rule, but I cannot figure out how to get it to work. Is there a way to automatically assign the approver upon ticket creation? If so, how would I go about this using a custom ticket rule as Approver is not one of the options available in the ticket wizard?
select HD_TICKET.*,
HD_TICKET.ID,
HD_TICKET.ID as TICKET_NUMBER,
HD_TICKET.TITLE as TICKET_TITLE,
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,
GROUP_CONCAT(OWNERS.EMAIL) as EMAILCOLUMN,
STATE,
if(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,
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(HD_TICKET.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_QUEUE_OWNER_LABEL_JT HDQOLJT on HD_TICKET.HD_QUEUE_ID = HDQOLJT.HD_QUEUE_ID
join USER_LABEL_JT ULJT on HDQOLJT.LABEL_ID = ULJT.LABEL_ID
join USER OWNERS on ULJT.USER_ID = OWNERS.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_STATUS.NAME = 'New') AND HD_TICKET.TITLE != '\" \"') AND HD_TICKET.CUSTOM_FIELD_VALUE1 != '\" \"') AND HD_TICKET.APPROVAL = 'Unassigned') and HD_TICKET.HD_QUEUE_ID = 50 )
Update SQL:
update HD_TICKET, HD_STATUS as T5, USER as T6
set HD_TICKET.HD_STATUS_ID = T5.ID,
HD_TICKET.TIME_OPENED = IF(T5.STATE = 'opened', NOW(), HD_TICKET.TIME_OPENED),
HD_TICKET.TIME_CLOSED = IF(T5.STATE = 'closed', NOW(), HD_TICKET.TIME_CLOSED),
HD_TICKET.TIME_STALLED = IF(T5.STATE = 'stalled', NOW(), HD_TICKET.TIME_STALLED),
HD_TICKET.SATISFACTION_RATING = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_RATING),
HD_TICKET.SATISFACTION_COMMENT = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_COMMENT),
HD_TICKET.APPROVER_ID = T6.ID
where T5.NAME = 'Awaiting Department Comments' and
HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID and
T6.FULL_NAME = 'Roshaye Harris' and
(HD_TICKET.ID in (<TICKET_IDS>))
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
Hobbsy
6 years ago
If you have the ID for the user that you want to set as the manager, you can create the rule using the wizard, set the criteria of the ticket that will define it needs to be sent to an approver, but in the update path just set any text as you can then replace it when the rule is complete.
Find out what ID in your user table your approver is i.e. 57
You can hardcode the value to be updated by adding a set value where your text is
set HD_TICKET.APPROVER_ID = 57
Once you have this working you can set the Approver field to Read only and only the ticket rule will be able to update the field.
Posted by:
Hobbsy
6 years ago
If you have the ID for the user that you want to set as the manager, you can create the rule using the wizard, set the criteria of the ticket that will define it needs to be sent to an approver, but in the update path just set any text as you can then replace it when the rule is complete.
Find out what ID in your user table your approver is i.e. 57
You can hardcode the value to be updated by adding a set value where your text is
set HD_TICKET.APPROVER_ID = 57
Once you have this working you can set the Approver field to Read only and only the ticket rule will be able to update the field.
