Trigger ticket rules based on change user label
I'd like to make a ticket rule that prevents users with a certain label from setting the ticket status to closed and I'm having trouble. Can anyone help?
This is sort of possible, you have to use the update log for the ticket that tracks the changes and whilst you cannot prevent the user setting the data value within the ticket, you can undo the change and let someone know that the change was made "illegally". So the logic is the ticket rule says if this data value is changed and the person who updated it is x, then set the value back to previous.
So I figured this out. I ended up using a role rather than a label but you couple probably alter easily enough. I just didn't want to deal with a table that was going to return a bunch of values when role was just as good for us. You'd probably handle that similarly to the Change table (see "C.ID=<CHANGE_ID>"); that part was important otherwise there is a result returned for every change ever associated with the record, which grows more or less exponentially for each ticket. Anyway, for posterity, here's the SQL:
HD_TICKET.ID, -- $id
UPDATER.ROLE_ID -- $updater_roleid
/* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
/* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
/* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
/* change field ******/ JOIN HD_TICKET_CHANGE_FIELD CF ON CF.HD_TICKET_CHANGE_ID = C.ID
UPDATER.ROLE_ID = 6 /*This is the technician's role*/
AND CF.AFTER_VALUE=118 /*This is our"closed" status*/
/* this is necessary because otherwise the change table returns a million things */
GROUP BY HD_TICKET.ID
set HD_TICKET.HD_STATUS_ID = 128 /*ID for the status I want to change to"*/
where (HD_TICKET.ID in (<TICKET_IDS>))