/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Update Status to Pending Response on ticket save.

02/26/2019 297 views

Hello,

I am trying to automatically update a ticket status to "Pending Response" when the ticket owner adds a comment to the ticket and saves it. I have created the following rule select and update SQL but I would like to check/get a list of the STATUS.ID's in the system since I have added custom ones. I cannot seem to find good documentation on the SQL Tables or how to directly query / access the database with SQL Manager, if that is possible.

SELECT STATEMENT:

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,32)


                   and (UPDATER.ID = OWNER.ID)


                   and UPDATER.ID > 0

UPDATE STATEMENT:

 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 = 'Awaiting Response' and


T.HD_QUEUE_ID = STATUS.HD_QUEUE_ID and  (T.ID in (<TICKET_IDS>))

0 Comments   [ + ] Show comments

Comments


Be the first to answer this question

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
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