Issue with SQL Code -- Please help
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:
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
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
# 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...
c.DESCRIPTION like concat("%", settings.ticket_label, " Created%")
or ifnull(cf.FIELD_CHANGED, "") = "STATUS_NAME"
) # ...on ticket close
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:
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.
Just to be sure, remember, custom fields in a script are always -1 from what you see in the queue configuration area. So presume your manager custom field is 43 in the UI.
If you are using a user lookup for that field than Im guessing Hobbsy might be right. If it is a single select then perhaps you are looking at the wrong field in the SQL DB.. if you havent factored in the -1 situation it should be 41 not 42.
Im not sure what your use case is, but consider that kace has a built in manager field for every user. We dont leverage it but it is there.