/build/static/layout/Breadcrumb_cap_w.png

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:

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


0 Comments   [ + ] Show comments

Answers (2)

Posted by: Hobbsy 3 months ago
Red Belt
0

It sounds as if you have the ID of the manager rather than the email address? So you need to add a join to the USER table, maybe using the manager ID so that you can get the email address to load into the variable.


Comments:
  • I appreciate your info.. the code I had above was working 100%, in the CTR I actually had this:
    HD_TICKET.TITLE, -- $title

    1 little stupid "," that I did not notice ugh!

    I have this rule set in 2 different CTR for 2 different "processes" and the other worked 100%, this one has some syntax error.... I looked between the 2 over and over and did not see my "," error

    Again thanks for your info...

    Good to know that the # in the custom field does convert to the E-mail address..

    :) - jct134 3 months ago
Posted by: barchetta 3 months ago
Fourth Degree Brown Belt
0

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. 


Comments:
  • In our case this is for a New hire so the user is not in Kace yet, so the tech doing the new hire select the manager in the field, so 1 part of the process sends e-mail to the manager.. and a second process for a change form if employee changes jobs etc.. also sends email to the new manager etc..

    I appreciate your info.. the code I had above was working 100%, in the CTR I actually had this:
    HD_TICKET.TITLE, -- $title

    1 little stupid "," that I did not notice ugh!

    I have this rule set in 2 different CTR for 2 different "processes" and the other worked 100%, this one has some syntax error.... I looked between the 2 over and over and did not see my "," error

    Again thanks for your info...

    Good to know that the # in the custom field does convert to the E-mail address..

    :) - jct134 3 months ago
 
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