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

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

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>)
Answered 03/04/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
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>)
Answered 03/04/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
1
Good point, Gerald. I just slapped a proof of concept together, but your elaboration is great!
Answered 03/05/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Thank you both for your answers for sure they helped me a lot.

Now the rules are up and running. :-)
Answered 03/05/2010 by: chris811
Orange Belt

Please log in to comment
Answer this question or Comment on this question for clarity