I am looking to create a few rules that will change a status to a reopened status when the user responds. For some reason it does not work. I am sure I am not changing something that needs to be changed. Could someone look this over and let me know what I am doing wrong? Basically, this rule will take a ticket that is currently in the "Opened" status and change it to the "Reopened" status when the user adds a comment and saves it. It works on another status but not any other ones I try it with. This is why I think I need to change something that I am unaware of.


Select Query

select distinct HD_TICKET.ID,
HD_TICKET.OWNER_ID as OWNER_ID,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.STATE as STATE,
OWNER.USER_NAME as OWNER_NAME,
OWNER.FULL_NAME as OWNER_FULLNAME,
OWNER.EMAIL as OWNER_EMAIL,
UPDATER.USER_NAME as UPDATERNAME,
UPDATER.EMAIL as UPDATEREMAIL
from (HD_TICKET, HD_STATUS)
left join HD_TICKET_CHANGE on HD_TICKET_CHANGE.ID =<CHANGE_ID>
left join HD_TICKET_CHANGE_FIELD on HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID = HD_TICKET_CHANGE.ID
left join USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID
left join USER UPDATER on UPDATER.ID = HD_TICKET_CHANGE.USER_ID
LEFT JOIN /*eligible owners*/(select EO.ID,Q.ID QID FROM HD_QUEUE Q JOIN HD_QUEUE_OWNER_LABEL_JT QL ON Q.ID=QL.HD_QUEUE_ID JOIN LABEL L ON L.ID=QL.LABEL_ID JOIN USER_LABEL_JT UL ON UL.LABEL_ID=L.ID JOIN USER EO ON EO.ID=UL.USER_ID) EOS ON UPDATER.ID=EOS.ID AND EOS.QID=HD_TICKET.HD_QUEUE_ID
where HD_STATUS.ID = HD_TICKET.HD_STATUS_ID
and HD_TICKET_CHANGE.HD_TICKET_ID= HD_TICKET.ID
and HD_TICKET_CHANGE_FIELD.FIELD_CHANGED !='SATISFACTION_RATING'
and HD_TICKET_CHANGE_FIELD.FIELD_CHANGED !='SATISFACTION_COMMENT'
and HD_STATUS.ID in (3) and EOS.ID IS NULL
and (UPDATER.ID <> OWNER.ID or OWNER.ID is NULL)
and UPDATER.ID > 0


Update Query

update HD_TICKET as T, HD_STATUS as STATUS
set T.HD_STATUS_ID = STATUS.ID,
T.RESOLUTION = CONCAT(T.RESOLUTION,'
Reopened'),
T.TIME_OPENED = IF(STATUS.STATE = 'opened', NOW(), T.TIME_OPENED),
T.TIME_CLOSED = IF(STATUS.STATE = 'closed', NOW(), T.TIME_CLOSED),
T.TIME_STALLED = IF(STATUS.STATE = 'stalled', NOW(), T.TIME_STALLED),
T.SATISFACTION_RATING = IF(STATUS.STATE = 'closed', NULL, T.SATISFACTION_RATING),
T.SATISFACTION_COMMENT = IF(STATUS.STATE = 'closed', NULL, T.SATISFACTION_COMMENT)
where STATUS.NAME = 'Opened' and
T.HD_QUEUE_ID = STATUS.HD_QUEUE_ID and (T.ID in (<TICKET_IDS>))
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
There is a built-in rule for this. You don't need to make a custom rule.

EDIT: It's listed as a "System Rule" named "CustomerResponded".
Answered 04/21/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
I am already using that one for another status. I was trying to make a couple other ones so that I could reopen them as well. Right now, it only opens the one status. If I was able to add other statuses into it, that would be fine as well. That’s actually where I got the code above from. I duplicated it and changed the status name in it. I also added what was listed in the System Rule to the duplicated one.
Answered 04/22/2010 by: ustacp
Second Degree Blue Belt

Please log in to comment
0
The system rule will allow you to choose multiple statuses for the "trigger", but you can obviously only choose one status to change to when the trigger status is used and a user adds a comment.

Do you have multiple "Reopened" statuses? If not, you can use the system rule - there is no need to create multiple rules. Hold the CTRL key and select multiple statuses for the trigger.
Answered 04/22/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Wow, don't I feel stupid now lol. I always try to do stuff the hard way instead of seeing if there is an easy way. I did not actually think you could select multiple statuses. I guess I never tried it either. That just made my morning! Thanks for the help!
Answered 04/22/2010 by: ustacp
Second Degree Blue Belt

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