Custom Rule to change the status of a ticket
Hello,
I'm looking for a customize rule to change the ticket status to Closed when the Solution field and Resolution Comments have date into them.
And also the another rule to change the ticket status to assigned once the owner field is diffrent than Unassigned.
Does anyone know a way to do this? I've been playing with the rules but not working yet ;(
Thank you in advance for your help.
I'm looking for a customize rule to change the ticket status to Closed when the Solution field and Resolution Comments have date into them.
And also the another rule to change the ticket status to assigned once the owner field is diffrent than Unassigned.
Does anyone know a way to do this? I've been playing with the rules but not working yet ;(
Thank you in advance for your help.
0 Comments
[ + ] Show comments
Answers (4)
Please log in to answer
Posted by:
airwolf
14 years ago
Good point, Gerald. I just slapped a proof of concept together, but your elaboration is great!
Posted by:
airwolf
14 years ago
In my examples, you'll need to make sure you modify the queue ID appropriately. I haven't tested these - use at your own risk.
There is no such thing as a Solution field unless you've created a custom field.
Select Query:
Update Query:
Select Query:
Update Query:
I'm looking for a customize rule to change the ticket status to Closed when the Solution field and Resolution Comments have date into them.
There is no such thing as a Solution field unless you've created a custom field.
Select Query:
SELECT ID FROM HD_TICKET
WHERE HD_TICKET.RESOLUTION != "" AND HD_TICKET.HD_QUEUE_ID = 1Update Query:
UPDATE HD_TICKET T
SET T.HD_STATUS_ID = (SELECT DISTINCT S.ID FROM HD_STATUS S WHERE S.NAME = "Closed" AND S.HD_QUEUE_ID = 1)
WHERE T.ID IN (<TICKET_IDS>)And also the another rule to change the ticket status to assigned once the owner field is diffrent than Unassigned.
Select Query:
SELECT ID FROM HD_TICKET
WHERE HD_TICKET.OWNER_ID != 0 AND HD_TICKET.HD_QUEUE_ID = 1Update Query:
UPDATE HD_TICKET T
SET T.HD_STATUS_ID = (SELECT DISTINCT S.ID FROM HD_STATUS S WHERE S.NAME = "Assigned" AND S.HD_QUEUE_ID = 1)
WHERE T.ID IN (<TICKET_IDS>)
Posted by:
GillySpy
14 years ago
Whenever modifying status in the update statement you should also modify the appropriate timestamp fields. This will take care of all of them and is queue agnostic...i am just going to expand on the update for example#2:
UPDATE HD_TICKET T JOIN HD_STATUS ON HD_STATUS.NAME = 'Assigned'
and HD_STATUS.HD_QUEUE_ID=T.HD_QUEUE_ID
SET T.HD_STATUS_ID = HD_STATUS.ID,
/*reset the state timstamps*/
T.TIME_OPENED = IF(HD_STATUS.STATE = 'opened', NOW(), T.TIME_OPENED),
T.TIME_CLOSED = IF(HD_STATUS.STATE = 'closed', NOW(), T.TIME_CLOSED),
T.TIME_STALLED = IF(HD_STATUS.STATE = 'stalled', NOW(), T.TIME_STALLED),
/*reset the survey*/
T.SATISFACTION_RATING = IF(HD_STATUS.STATE = 'closed', NULL, T.SATISFACTION_RATING),
T.SATISFACTION_COMMENT = IF(HD_STATUS.STATE = 'closed', NULL, T.SATISFACTION_COMMENT)
WHERE T.ID IN (<TICKET_IDS>)
Posted by:
chris811
14 years ago
Thank you both for your answers for sure they helped me a lot.
Now the rules are up and running. :-)
Now the rules are up and running. :-)
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.