I am trying to adjust the CustomerResponded Rule that comes pre-installed on the kbox. We have a "Waiting on Response" status we use when customers don't give us enough information. I want to be able to put Unassigned tickets into "Waiting on Response" so that our technicians will see tickets that are waiting on more information. Then they can take ownership, if they want or they can wait for more information.

The pre-installed rule kicks the ticket to "Follow-up" whenever it is updated by a non-owner. Well, if it is unassigned, everyone is a non-owner and trying to put it in the status "Waiting on Response" is an update from a non-owner and kicks it to follow-up automatically. I'm trying to figure out a workaround to allow Unassigned tickets to be in a "Waiting" status. Here's the SQL:

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
                     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 (10,6,11)
                       and (UPDATER.ID <> OWNER.ID or OWNER.ID is NULL)

Update:

update HD_TICKET as T, HD_STATUS as STATUS
set T.HD_STATUS_ID = STATUS.ID,
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 = 'Follow-up' and
T.HD_QUEUE_ID = STATUS.HD_QUEUE_ID and  (T.ID in (<TICKET_IDS>))

Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • this works great, do that now when another engineer puts a comment in the ticket it doesn't change the status, but when i got the user "submitter" of the ticket to add their comment it didn't change the status. if there something else that need to be added to get this part to work?
Please log in to comment

Answers

1

Took me a while, but I just figured out that if you just add "and OWNER.ID != 0" then the rule will only apply if the ticket is assigned to someone.

Answered 10/01/2013 by: lmland
Tenth Degree Black Belt

  • Isn't SQL fun? :)

    Good on you for updating the question :)
  • this works great, do that now when another engineer puts a comment in the ticket it doesn't change the status, but when i got the user "submitter" of the ticket to add their comment it didn't change the status. if there something else that need to be added to get this part to work?
    • I managed to work this out by changing:

      and (UPDATER.ID= HD_TICKET.SUBMITTER_ID)
Please log in to comment
Answer this question or Comment on this question for clarity