I've tried two rules, for the first rule, <TICKET_IDS> return HD_TICKET_CHANGE.ID, the second return HD_TICKET.ID, I'm really confused for that, anyone who can help that, thanks!

Rule 1 for Queue 1

select HD_TICKET.ID, HD_TICKET.TITLE, HD_TICKET.CUSTOM_FIELD_VALUE0, C.ID, C.DESCRIPTION, CF.FIELD_CHANGED, CF.BEFORE_VALUE, CF.AFTER_VALUE
from HD_TICKET
join HD_TICKET_CHANGE C on HD_TICKET.ID=C.HD_TICKET_ID and C.ID=<CHANGE_ID>
join HD_TICKET_CHANGE_FIELD CF on C.ID=CF.HD_TICKET_CHANGE_ID
where
HD_TICKET.HD_QUEUE_ID=8 and CF.FIELD_CHANGED="STATUS_NAME" and CF.BEFORE_VALUE!="Closed" and CF.AFTER_VALUE="Closed"

update HD_TICKET
join HD_TICKET_CHANGE C on HD_TICKET.ID=C.HD_TICKET_ID and C.ID=<TICKET_IDS>
set
HD_TICKET.CUSTOM_FIELD_VALUE0 = CONCAT('a', HD_TICKET.CUSTOM_FIELD_VALUE0)

Rule 2 for Queue 2

select HD_TICKET.ID, HD_TICKET.TITLE, HD_TICKET.CUSTOM_FIELD_VALUE0, HD_TICKET.CUSTOM_FIELD_VALUE1
from HD_TICKET
inner join HD_TICKET_CHANGE C on HD_TICKET.ID=C.HD_TICKET_ID and C.ID=<CHANGE_ID>
where
HD_TICKET.HD_QUEUE_ID=9 and HD_TICKET.CUSTOM_FIELD_VALUE0=1 and HD_TICKET.CUSTOM_FIELD_VALUE1!=""

update HD_TICKET
join HD_TICKET_CHANGE C on C.HD_TICKET_ID=HD_TICKET.ID
set
HD_TICKET.CUSTOM_FIELD_VALUE0="",
HD_TICKET.CUSTOM_FIELD_VALUE2=CONCAT('--- ', NOW(), ' ---', '  Email to user as: ', HD_TICKET.CUSTOM_FIELD_VALUE1, '   **********           ', HD_TICKET.CUSTOM_FIELD_VALUE2),
HD_TICKET.CUSTOM_FIELD_VALUE1=""
where HD_TICKET.ID=<TICKET_IDS>

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

The <TICKET_IDS> value will get set to the IDs of the tickets returned by the select rule. The value for <TICKET_IDS> doesn't make a difference in Rule 1 because you aren't changing anything in the HD_TICKET_CHANGE table so the broken join statement is kind of moot. 

Answered 04/28/2014 by: chucksteel
Red Belt

  • I do another test, if in the select sentence, I only choose HD_TICKET table column, not choose HD_TICKET_CHANGE column, then the <TICKET_IDS> return HD_TICKET.ID. So it seems select sentence is the root cause for the return <TICKET_IDS>, may I right?
    • Yes, the results from selecting HD_TICKET.ID is placed into the <TICKET_IDS> variable. I seem to recall that whatever is returned as ID is actually what is placed in the <TICKET_IDS> variable so if you did something like SELECT USER.ID FROM USERS then <TICKET_IDS> would actually contain the user IDs selected.
      • Great, thanks for your reply!
Please log in to comment
Answer this question or Comment on this question for clarity