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

Comments

Please log in to comment

Answers

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>

Answered 12/28/2012 by: jverbosk
Red Belt

  • @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.
  • I figured out the reason for the error.
    It's working now :)

    Thanks again for your help
    • Could you possibly post how you resolved this? I am working on a similar issue.
  • 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
    • 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.
  • 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
Please log in to comment
Answer this question or Comment on this question for clarity