/build/static/layout/Breadcrumb_cap_w.png
07/13/2017 608 views
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

Comments


All Answers

0
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.

Answered 07/13/2017 by: Hobbsy
Red Belt

0
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.

Answered 07/13/2017 by: Hobbsy
Red Belt