How do I write a custom SQL rule to pass the comments from a parent in to the comments of a child?
I have seen this question asked as a follow up question in several places on here but it is never really answered. I have a process for new hires that creates a parent and several child tickets. I need to be able to pass the comments (im already passing custom fields) from the parent ticket to each child ticket (preferably on ticket save).
In other words, i need to be able to see each parent ticket comment in the comments section of each child ticket. I'm very limited in my SQL ability so an explanation of any code provided for this issue would be greatly appreciated.
1 Comment
[ + ] Show comment
-
I have asked this question several times and have not received an answer or been able to get this to work properly. Have you resolved making this work? - scarpent 8 years ago
Answers (1)
Please log in to answer
Posted by:
h2opolo25
8 years ago
On the child ticket queue...
Select Statement:
SELECT PARENT.TITLE,
HD_TICKET.TITLE,
HD_TICKET.ID,
PARENT.CUSTOM_FIELD_VALUE1,
PARENT.CUSTOM_FIELD_VALUE2,
PARENT.CUSTOM_FIELD_VALUE3,
PARENT.CUSTOM_FIELD_VALUE4,
PARENT.CUSTOM_FIELD_VALUE6,
PARENT.CUSTOM_FIELD_VALUE7,
PARENT.CUSTOM_FIELD_VALUE8,
C.COMMENT
FROM HD_TICKET PARENT, HD_TICKET, HD_TICKET_CHANGE C
WHERE PARENT.ID = HD_TICKET.PARENT_ID
and PARENT.HD_QUEUE_ID = 9
and C.HD_TICKET_ID = HD_TICKET.ID
AND HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 10 SECOND)
Update Statement:
UPDATE HD_TICKET PARENT,HD_TICKET_CHANGE C, HD_TICKET
SET HD_TICKET.TITLE = PARENT.TITLE,
C.COMMENT = concat(
'\n Office: ',
PARENT.CUSTOM_FIELD_VALUE1,
'\n Company: ',
PARENT.CUSTOM_FIELD_VALUE2,
'\n EmployeeID: ',
PARENT.CUSTOM_FIELD_VALUE3,
'\n GP Account: ',
PARENT.CUSTOM_FIELD_VALUE5,
'\n Time Entry Account: ',
PARENT.CUSTOM_FIELD_VALUE6,
'\n Pivots Account: ',
PARENT.CUSTOM_FIELD_VALUE7,
'\n Bid Log Account: ',
PARENT.CUSTOM_FIELD_VALUE8
)
WHERE PARENT.ID = HD_TICKET.PARENT_ID
and C.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET.HD_QUEUE_ID = 4
AND HD_TICKET.ID IN (<TICKET_IDS>)
OnTicket Save
This one transfers the bold fields into the comment section of the child ticket. You can also assign fields from the child ticket to receive the values from the parent ticket.
Comments:
-
Thanks for responding however I have seen this answer before and I dont think it addresses my question. Please correct me if im wrong but it looks like this code will copy out custom fields from the Parent to the Comments section of the child. If so this does not help as I need to be able to copy Comments from the parent not custom fields. I then need those parent comments to show up as comments in the child ticket. - svargas 8 years ago
-
Same basic concept, just need to substitute the custom fields for the comments in the parent ticket and most likely change the TICKET.CREATED line with whatever timestamp the comment adheres to. I'll try to get you more exact code tomorrow sometimes if someone doesn't have it already. - h2opolo25 8 years ago
