/build/static/layout/Breadcrumb_cap_w.png

Issue trying to use ticket custom field data in email output from custom ticket rule

Here is the code I have so far


select

  distinct HD_TICKET.ID,

HD_TICKET.CUSTOM_VALUE4 AS PHONE,

HD_TICKET.CUSTOM_VALUE1 AS USERNAME,

HD_TICKET.CUSTOM_VALUE2 AS PASSWORD,

HD_TICKET.CUSTOM_VALUE3 AS EMAIL,

  case

    when HD_TICKET.SUMMARY like "%Company: company1%" then "Email1@somewhere.com"

    when HD_TICKET.SUMMARY like "%Company: company2%" then "Email2@somewhere.com"

    when HD_TICKET.SUMMARY like "%Company: company3%" then "Email3@somewhere.com"

    when HD_TICKET.SUMMARY like "%Company: company4%" then "Email4@somewhere.com"

    when HD_TICKET.SUMMARY like "%Company: company5%" then "Email5@somewhere.com"

    else "Email@somewhere.com"

  end as NEWTICKETEMAIL -- $newticketemail


from

  HD_TICKET

 join HD_TICKET_CHANGE c on HD_TICKET.ID = c.HD_TICKET_ID and c.ID = <CHANGE_ID>

  left join HD_TICKET_CHANGE_FIELD cf on c.ID = cf.HD_TICKET_CHANGE_ID

  join HD_STATUS S on HD_TICKET.HD_STATUS_ID = S.ID

  join HD_SERVICE_TICKET st on HD_TICKET.SERVICE_TICKET_ID = st.ID

  join HD_SERVICE serv on st.HD_SERVICE_ID = serv.ID

  ,(select S.`VALUE` ticket_label from SETTINGS S where S.`NAME` = "HD_TICKET_LABEL") settings

where


  HD_TICKET.PARENT_ID = 0 and HD_TICKET.IS_PARENT # Parent Ticket

  and serv.NAME = "Main Employee Process" # of process "Main Employee Process"

  and S.STATE = "closed" # on ticket close...

  and (

    c.DESCRIPTION like concat("%", settings.ticket_label, " Created%")

    or ifnull(cf.FIELD_CHANGED, "") = "STATUS_NAME"

  ) # ...on ticket close

group by

  HD_TICKET.ID



The error I get during the select is:11/23/2021 16:24:01> Starting: 11/23/2021 16:24:01 11/23/2021 16:24:01> Executing Select Query... 11/23/2021 16:24:01> mysqli error: [1054: Unknown column 'HD_TICKET.CUSTOM_VALUE4' in 'field list'] in EXECUTE("select distinct HD_TICKET.ID, HD_TICKET.CUSTOM_VALUE4 AS PHONE, HD_TICKET.CUSTOM_VALUE1 AS USERNAME, HD_TICKET.CUSTOM_VALUE2 AS PASSWORD, HD_TICKET.CUSTOM_VALUE3 AS EMAIL, etc.. 
If anyone has any ideas I would appreciate it
thanks
Jason

0 Comments   [ + ] Show comments

Answers (2)

Answer Summary:
Posted by: Hobbsy 2 years ago
Red Belt
0

Top Answer

I think it should be

HD_TICKET.CUSTOM_FIELD_VALUE4 Etc etc



Comments:
  • I really appreciate your help.. I think the select is working now.. but when I use the variables in my html e-mail that gets sent, it does not work..

    Here is part of the e-mail code:
    <table border="1" cellpadding="0" cellspacing="0" style="background-color:transparent;border-spacing:0px;border:0px;border-collapse:collapse;max-width:100%;" width="700">
    <tbody>
    <tr>
    <td style="min-width:5px;border:1px solid rgb(221,221,221);"><strong style="font-weight:700;">Computer Login Information&nbsp;</strong><em style="font-style:italic;">(See step 3 above for other account info)</em></td>
    </tr>
    <tr>
    <td style="min-width:5px;border:1px solid rgb(221,221,221);">Windows Username: $USERNAME</td>
    </tr>
    <tr>
    <td style="min-width:5px;border:1px solid rgb(221,221,221);">Windows Password:$PASSWORD</td>
    </tr>


    The resulting E-mail:
    Computer Login Information (See step 3 above for other account info)
    Windows Username: $USERNAME
    Windows Password:$PASSWORD

    again, thanks for any ideas you may have.. - jct134 2 years ago
    • Try the variable in lower case in the email text i.e. $password rather the $PASSWORD - Hobbsy 2 years ago
Posted by: jct134 2 years ago
Senior Purple Belt
0

I ended up using this in my select statement:


select

  distinct HD_TICKET.ID,

HD_TICKET.CUSTOM_FIELD_VALUE0 AS TICKET_CUSTOM_1,

HD_TICKET.CUSTOM_FIELD_VALUE1 AS TICKET_CUSTOM_2,

HD_TICKET.CUSTOM_FIELD_VALUE2 AS TICKET_CUSTOM_3,

HD_TICKET.CUSTOM_FIELD_VALUE3 AS TICKET_CUSTOM_4,

HD_TICKET.TITLE, -- $title



And in my E-mail template I used these variables..

$ticket_custom_1

$ticket_custom_2

$ticket_custom_3

$ticket_custom_4



All is working perfectly now...





 
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