/build/static/layout/Breadcrumb_cap_w.png

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





0 Comments   [ + ] Show comments

Answers (2)

Answer Summary:
Posted by: IgorAngelini 9 months ago
Second Degree Blue Belt
0

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 9 months 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 9 months 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 9 months 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 9 months 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 9 months 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 9 months 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 9 months 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 9 months 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 9 months ago
Posted by: IgorAngelini 9 months ago
Second Degree Blue Belt
0

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 9 months 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 9 months ago
      • You're welcome. Maybe this thread will help someone else with a similar problem in the future. - IgorAngelini 9 months 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