Need help with parent-child ticket rule (notify child ticket owners when parent ticket approval set)
Hoping someone can help, I'm pretty much at a loss with this one....
I'm trying to write a custom ticket rule that will email the owners of child tickets when the corresponding parent ticket is initially approved and saved (it's just a placeholder that doesn't get updates beyond this). Everything works as expected in the MySQL Query Browser and when I use "Run Now" in the ticket rule's Edit Detail screen. However, if the ticket rule runs as scheduled (or on Ticket Save, which is the goal), it just says "Executing Select Query..." in the Run Log and has the following entry in the K1000 Server Logs:
[Tue Jun 12 16:25:56 2012] [error] [client 192.168.11.52] PHP Warning: sprintf() [<a href='function.sprintf'>function.sprintf</a>]: Too few arguments at /kbox/kboxwww/include/KLocale.class.php(855) : eval()'d code:10, referer: http://kbox/adminui/ticket_rule.php?ID=44
I researched "Too few arguments" and My SQL and landed in a mess of potential bugs, so I'm at a loss. Any suggestions (or fixes) would be very greatly appreciated.
Additional notes - I've already tried rebooting the KBOX in case something was stuck. Amusingly, when this rule is setup in the Child queue (queue 1), it runs fine when a ticket is updated. Unfortunately, setup like that it doesn't catch the Parent queue (queue 2) ticket being saved. So this has to be run from the Parent queue in order to catch the ticket save - which it *is* doing (based on the Starting time in the Run Log), it just doesn't complete the run.
* Update and further thoughts in my answer below.
Thanks very much!!!
Original code - tweaked in answer below and executing now, but still having an issue.
select USER.EMAIL as OWNER_EMAIL, PARENT.ID AS ID, PARENT.TITLE as ISSUE, PARENT.APPROVAL as APPROVAL,
PARENT.CUSTOM_FIELD_VALUE0 as LOCATION, PARENT.CUSTOM_FIELD_VALUE2 as FULLNAME, PARENT.CUSTOM_FIELD_VALUE3 as DEPARTMENT,
PARENT.CUSTOM_FIELD_VALUE4 as JOBTITLE, PARENT.CUSTOM_FIELD_VALUE5 as MANAGER, PARENT.CUSTOM_FIELD_VALUE6 as DATEOFHIRE,
PARENT.CUSTOM_FIELD_VALUE13 as REMOTE, DATE_FORMAT(PARENT.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED,
if ((LENGTH(USER.FULL_NAME) = 0), USER.USER_NAME, USER.FULL_NAME) as OWNER_NAME, USER.EMAIL as OWNER_EMAIL
from HD_TICKET PARENT, HD_TICKET CHILD
LEFT JOIN USER on USER.ID = CHILD.OWNER_ID
where PARENT.ID = CHILD.PARENT_ID
and PARENT.APPROVAL != ''
Reference the child tickets via a join statement. Thanks again, Chuck! Working Select Query: select USER.EMAIL as OWNER_EMAIL, HD_TICKET.ID AS ID, HD_TICKET.TITLE as ISSUE, HD_TICKET.APPROVAL as APPROVAL, HD_TICKET.CUSTOM_FIELD_VALUE0 as LOCATION, HD_TICKET.CUSTOM_FIELD_VALUE2 as FULLNAME, HD_TICKET.CUSTOM_FIELD_VALUE3 as DEPARTMENT, HD_TICKET.CUSTOM_FIELD_VALUE4 as JOBTITLE, HD_TICKET.CUSTOM_FIELD_VALUE5 as MANAGER, HD_TICKET.CUSTOM_FIELD_VALUE6 as DATEOFHIRE, HD_TICKET.CUSTOM_FIELD_VALUE13 as REMOTE, DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, if ((LENGTH(USER.FULL_NAME) = 0), USER.USER_NAME, USER.FULL_NAME) as OWNER_NAME, USER.EMAIL as OWNER_EMAIL from HD_TICKET JOIN HD_TICKET CHILD on CHILD.PARENT_ID=HD_TICKET.ID LEFT JOIN USER on USER.ID = CHILD.OWNER_ID where (HD_TICKET.APPROVAL != '' and CHILD.HD_QUEUE_ID = 1 and HD_TICKET.HD_QUEUE_ID = 2)
Community Chosen Answer
I think that if you reference the child tickets via a join you might have better luck. So instead of from HD_TICKET PARENT, HD_TICKET, just have from HD_TICKET join HD_TICKET CHILD on CHILD.PARENT_ID = HD_TICKET.ID, or something like that.