Scripting

So, I thought I had this working...

I have a custom field in a ticket queue named "Manager" that we can select the manager's name from...

in a Custom Ticket Rule, I want to set this "User-Manager" into a variable to e-mail info when the ticket is closed..

Here is what I have in the SQL:

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.CUSTOM_FIELD_VALUE42 AS MANAGEREMAIL,

HD_TICKET.TITLE -- $title

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

  HD_TICKET.PARENT_ID > 0 and !HD_TICKET.IS_PARENT # Child Ticket

  and HD_TICKET.TITLE like "%CHANGE Accounts%" # title contains "CHANGE Accounts"

  and serv.NAME = "Change Employee Process" # of process "Change 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

In the E-mail I use:

$title in the Subject

MANAGEREMAIL in the Column containing email addresses

In the body of the Email I use:

$ticket_custom_1

$ticket_custom_2

$ticket_custom_3

When the ticket rule runs, it says an e-mail was sent.. however.. nothing ever shows up...

Then when looking through the database I found that my "HD_TICKET.CUSTOM_FIELD_VALUE42" contains a number not the e-mail address of the user like the CC list does...

So I have 2 options.. swap what I send out to the CC list with this "HD_TICKET.CUSTOM_FIELD_VALUE42" field..  or find a way to join the User table to the value in the HD_TICKET.CUSTOM_FIELD_VALUE42 and set the MANAGEREMAIL = to the User.EMAIL field..

With the code I have above is it possible to also add the code to make that happen??

Any ideas are appreciated.

J