When ticket is closed, pull the comment from the parent ticket into variable to use in sending e-mail
So I have the SQL below.. along with what in in the body of the "email" that gets sent..
The issue is the $comment variable has nothing in it, I think cause the actual comment is in the parent ticket and is just copied to the child ticket through the ticket process, so I think I need to find a way to pull the parent comment when this child ticket gets closed.. to use in the sent email
Hope that makes sense..
If anyone has any idea on how I can do this please let me know!
Thanks
J
Here is the Select SQL and what is in the E-mail body:
SELECT HD_TICKET.*,
-- ticket fields
HD_TICKET.CUSTOM_FIELD_VALUE0,
HD_TICKET.ID, -- $id
HD_TICKET.ID AS TICKNUM, -- $ticknum
HD_TICKET.TITLE, -- $title
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified
-- change fields
C.COMMENT, -- $comment
C.DESCRIPTION, -- $description
GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://k1000/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history
-- about the updater
UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
UPDATER.EMAIL AS UPDATER_EMAIL, -- $updater_email
IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional
-- about the owner
OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
OWNER.EMAIL AS OWNER_EMAIL, -- $owner_email
IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user
-- about the submitter
SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
SUBMITTER.EMAIL AS SUBMITTER_EMAIL, -- $submitter_email
-- about priority
P.NAME AS PRIORITY, -- $priority
-- about status
S.NAME AS STATUS, -- $status
-- about impact
I.NAME AS IMPACT, -- $impact
-- about category
CAT.NAME AS CATEGORY, -- $category
-- example of static distribution list
'Someone@somewhere.com' AS NEWTICKETEMAIL -- $newticketemail
FROM HD_TICKET
/* latest change ***/ join HD_TICKET_CHANGE c on HD_TICKET.ID = c.HD_TICKET_ID and c.ID = <CHANGE_ID>
/* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
/* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
/* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
/* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
/* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
/* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
/* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
/* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
WHERE
HD_TICKET.PARENT_ID > 0 and !HD_TICKET.IS_PARENT # Child Ticket
and ((HD_TICKET.TITLE like "%User Accounts%") OR (HD_TICKET.TITLE like "%Change%")) # title Contains "User Accounts" or "Change"
and ( HD_TICKET.SUMMARY like '%SomeCompany%')
and S.STATE = "closed"
and (
c.DESCRIPTION like concat("%", settings.ticket_label, " Created%")
or ifnull(cf.FIELD_CHANGED, "") = "STATUS_NAME"
)
/* this is necessary when using group by functions */
GROUP BY HD_TICKET.ID
HAVING 1=1
Body of Email:
Employee's Active Directory User ID: $custom_field_value0
$comment
Answers (2)
Have you tried selecting like this?
{...}
PARENT.COMMENT
{...}
LEFT JOIN HD_TICKET PARENT
ON PARENT.ID = HD_TICKET.PARENT_ID
This way you will pull the comment from the parent ticket ID
Give it a shot, tell me what happens
Comments:
-
That did not work.. I get an error "Executing Select Query... 02/14/2022 13:57:09> mysqli error: [1142: SELECT command denied to user 'blahblah@blah' for table 'TICKET']"
I added the PARENT.COMMENT, to the select ticket fields area, and the LEFT JOIN to the FROM with the other JOIN's
in the Database, I do not see any PARENT "Table" so not sure that would work any way?
Thanks
J - jct134 2 years ago-
It's a self referenced table, the HD_TICKET.PARENT_ID field maps to a HD_TICKET.ID field, so we need to call a "temporary" table to get the PARENT data. That's why I'm joining the HD_TICKET table again, but with an alias, in the case PARENT, now we have two "copies" of HD_TICKET, one for the child one for the parent. (The ASSET table also works like this).
And I see what is wrong, my bad, there is a typo there, try:
LEFT JOIN HD_TICKET PARENT
ON PARENT.ID = HD_TICKET.PARENT_ID - IgorAngelini 2 years ago-
I am comparing your original:
LEFT JOIN HD_TICKET PARENT
ON PARENT.ID = HD_TICKET.PARENT_ID
and do not see any difference to what you posted here:
LEFT JOIN HD_TICKET PARENT
ON PARENT.ID = HD_TICKET.PARENT_ID
I am sorry if I do not see it, but what is different?
Thanks
J - jct134 2 years ago -
Also, I think this may not work even if I do get the comment form the parent, as the last comment simply says that the parent was closed by the child etc.. I would actually need the "first comment" so that I am not sure is even possible? - jct134 2 years ago
-
It's the same because I edited the answer to fix the typo, it was "HD.TICKET PARENT" instead of the correct "HD_TICKET PARENT" (that's why your error said"table 'TICKET'")
I've read your query again more carefully.
So let me make some questions.
What triggers the email? If it's a change, it's the change on the parent or child ticket? In this change are there any data on HD_TICKECT_CHANGE.COMMENT?
Go to HD_TICKET_CHANGE on MySQL Workbench or Beekeeper Studio and see the change with the comment info that you want. Note the HD_TICKET_ID, is it the parent's or child's ID?
I think there is a way, but it is quite different from what I thought at the beginning. - IgorAngelini 2 years ago -
So, when a child ticket that had title with either User Accounts, or Change in the title closes, I want to send the main info from the parent ticket to our sister queue, I have it sending the "summary" right now which works, but is not clean and pretty...
thanks again
j - jct134 2 years ago -
I made the changes, and still get the same error
SELECT command denied to user 'xx'@'localhost' for table 'TICKET']
I guess I am just stuck with what I already had.. it works, but I wanted it to be a bit better..
Thanks for you efforts
J - jct134 2 years ago -
Ok, so me see if I got what you want
You close a child ticket, and want to send its parent's "main info" with an email.
So, is this "main info" the summary of the parent ticket, its last comment or the comment chain?
If is the comment chain, I don't think it's possible, but the parent's last comment and summary is. - IgorAngelini 2 years ago -
So we do this a few different times, we have 3 different "process templates" when an e-mail comes in that triggers either process, 4 tickets are created, 1 is a parent ticket, 3 child tickets. a CTR that triggers first takes the parent ticket "Comment" and e-mails it out, and it looks just like it should, the parent ticket is then moved to a "parent" queue and is just auto closed when all the child tickets are closed.. when one of the child gets closed I have a CTR that then sends this info (if certain criteria is met) into our sister company ticket queue through e-mail, and when I used the same process to get the "Comment" from the ticket like I did at the beginning of the process there seems to be nothing in the "Comment", however I can use the "Summary" but it does not have nice formatting etc.. and is just all lumped together in 1 long sentence... I think it might be because the child stuff was copied from the parent originally? I am not sure.. but I can not get the "Comment" to show as anything. so thought if I could get the Comment form the parent, it would maybe work better? - jct134 2 years ago
Top Answer
Ok, so let's try again, I've made a query that when the ticket is not a child of any other ticket, it acts like your query, because you say that it work, and when it's a child ticket it gets the Parents comment info.
So try adding this to your existing query:
SELECT (CASE HD_TICKET.PARENT_ID
WHEN 0 THEN C.COMMENT
ELSE PARENT.COMMENT
END) AS COMMENT
LEFT JOIN (SELECT COMMENT,
HD_TICKET_ID
FROM HD_TICKET_CHANGE
WHERE COMMENT != "") PARENT
ON PARENT.HD_TICKET_ID = HD_TICKET.PARENT_ID
If your query is made specifically for child tickets, just change the SELECT to:
SELECT PARENT.COMMENT AS COMMENT
Tell me if any error occurred or if it worked
Comments:
-
Did it work? - IgorAngelini 2 years ago
-
I actually ended up switching it around a little bit.. and instead of the using the $summary I found that $html_summary has a nice formatted version of the summary and worked just great without even trying to pull from parent ticket..
I did then have an issue, where this Queue is then the "submitter" when it e-mail into the sister company queue, and when their queue triggers off the default "thanks for submitting a ticket blah blah" to the submitter it was causing a new ticket to be created because there was an error where the sister co queue did not have permission to submit in to this queue and we do not what them to.. so I have to create another CTR on the sister co queue to automatically change the submitter to a service account user we use for some power app ticket submissions which then allows the sister co queue to send out the "thanks for submitting ticket blah blah" with no error...
Thanks again for all your assistance.. much appreciated :)
J - jct134 2 years ago-
You're welcome. Maybe this thread will help someone else with a similar problem in the future. - IgorAngelini 2 years ago
