/build/static/layout/Breadcrumb_cap_w.png

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.

0 Comments   [ + ] Show comments

Answers (4)

Posted by: airwolf 14 years ago
Red Belt
1
Good point, Gerald. I just slapped a proof of concept together, but your elaboration is great!
Posted by: airwolf 14 years ago
Red Belt
0
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.

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 = 1


Update 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 = 1


Update 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
7th Degree Black Belt
0
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
Orange Belt
0
Thank you both for your answers for sure they helped me a lot.

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.
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ