/build/static/layout/Breadcrumb_cap_w.png

Passing comment field from parent ticket to child tickets

I am trying to pass data from the parent ticket to the child tickets in KAC and found this article helpful. http://www.itninja.com/blog/view/passing-data-from-parent-tickets-to-child-tickets

Does anyone know how to pass the comments from the parent ticket to the child ticket?

I haven't been able to find the comment field in the KACE database.

Thank you


0 Comments   [ + ] Show comments

Answers (1)

Posted by: jverbosk 11 years ago
Red Belt
3

I can't guarantee that this is a working answer as I don't have a test system to play with, but hopefully this will get you close enough to have it working if you play with it.  The first Select query is to show where the comments are located in relation to the tickets.  The second query is an informational Select query (what I would call a "research" or "work in progress query") which lists all of the child and associated parent tickets with comments.  The last Select query is what I would suggest trying in an actual ticket rule.

The WHERE statements (aside from the implied JOINs) include a few filters:

1) AND PCHANGE.USER_ID != 0 >>> this says "only show parent ticket comments added by users" (i.e. don't show comments added by the system, USER_ID = 0)

2) AND PCHANGE.COMMENT != '' >>> this says "don't show empty/blank comments"

3) AND HD_TICKET.HD_QUEUE_ID = 1 >>> this says only target (child) tickets in the first queue (something you might want or not, but including it just in case)

The Update query is what I'm unable to test and can't guarantee will work, as it needs to target the HD_TICKET_CHANGE table which the Update query may not like.  This is just an "on ticket save" proof of concept query, so tread with caution here.  Of course, if you have a test system (or have time to recover from backups), play away!   ^_^

Hope that at least helps you get started!

John

_____________________________________

Initial Select Query:
SELECT HD_TICKET.ID, HD_TICKET.TITLE, HD_TICKET_CHANGE.COMMENT
FROM HD_TICKET
JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
WHERE HD_TICKET_CHANGE.USER_ID != 0
AND HD_TICKET_CHANGE.COMMENT != ''
AND HD_TICKET.HD_QUEUE_ID = 1
ORDER BY HD_TICKET.ID

Informational Select Query:
SELECT HD_TICKET.ID, PARENT.ID,
HD_TICKET.TITLE, PARENT.TITLE AS PTITLE,
HD_TICKET_CHANGE.COMMENT, PCHANGE.COMMENT AS PCOMMENT
FROM HD_TICKET PARENT, HD_TICKET, HD_TICKET_CHANGE PCHANGE, HD_TICKET_CHANGE
WHERE HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
AND PCHANGE.HD_TICKET_ID = PARENT.ID
AND PARENT.ID = HD_TICKET.PARENT_ID
AND PCHANGE.USER_ID != 0
AND PCHANGE.COMMENT != ''
AND HD_TICKET.HD_QUEUE_ID = 1
ORDER BY HD_TICKET.ID

Select Query:
SELECT HD_TICKET.ID, HD_TICKET_CHANGE.COMMENT, PCHANGE.COMMENT AS PCOMMENT
FROM HD_TICKET PARENT, HD_TICKET, HD_TICKET_CHANGE PCHANGE, HD_TICKET_CHANGE
WHERE HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
AND PCHANGE.HD_TICKET_ID = PARENT.ID
AND PARENT.ID = HD_TICKET.PARENT_ID
AND PCHANGE.USER_ID != 0
AND PCHANGE.COMMENT != ''
AND HD_TICKET.HD_QUEUE_ID = 1
ORDER BY HD_TICKET.ID

Update Query:
UPDATE HD_TICKET PARENT, HD_TICKET, HD_TICKET_CHANGE PCHANGE, HD_TICKET_CHANGE
SET HD_TICKET_CHANGE.COMMENT = PCHANGE.COMMENT
WHERE HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
AND PCHANGE.HD_TICKET_ID = PARENT.ID
AND PARENT.ID = HD_TICKET.PARENT_ID
AND HD_TICKET.ID = <TICKET_IDS>


Comments:
  • @jverbosk, Thank you so much for your help!
    The select queury appears to be working but I'm just having a problem with the update queury.
    I'm getting a mysql error 1064.
    I'll keep playing around with it and hopefully will figure it out. - mstoyles 11 years ago
  • I figured out the reason for the error.
    It's working now :)

    Thanks again for your help - mstoyles 11 years ago
    • Could you possibly post how you resolved this? I am working on a similar issue. - young020 11 years ago
  • Did that work the way I posted, or did you have to make adjustments? Mainly curious, as this is my update query to update a non-HD_TICKET field. If you had to make any changes, please advise on what was changed.

    Thanks!

    John - jverbosk 11 years ago
    • This worked great as written thanks.

      It does toss an error 1064 if there are multiple comments since the last
      AND HD_TICKET.ID=<TICKET_IDS> since <TICKET_IDS> returns multiple values at that point. But for passing the initial comment it works great. - cb.sean 11 years ago
  • If multiple values are being passed for HD_TICKET_CHANGE.COMMENT, you could try using LIMIT 1 in the query. Probably would require a rewrite, but food for thought.

    John - jverbosk 11 years ago

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