/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


How do I write a custom SQL rule to pass the comments from a parent in to the comments of a child?

08/08/2015 1107 views
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

Comments

  • 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?

All Answers

1
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.
Answered 08/09/2015 by: h2opolo25
Red Belt

  • 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.
    • 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.

Don't be a Stranger!

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

Sign up! or login

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