/build/static/layout/Breadcrumb_cap_w.png

Making Customize Emails with columns in HD_TICKET as variables

OK so i ask this Question http://www.itninja.com/question/email-on-call-staff-when-a-new-ticket-getes-created-by-user

and JasonEgg did a great rule for me on-call over the weekend. I have is texting my iphone but i can't get variables in the text msg.

so if i go to Help-Desk -> Configuration -> Rules

Then click on are New IT on Call rule then with the option "Email each recipient in query results"

I can enter a Subject: and the Message: i want to sent when a ticket get is created.

How can i added the tickets info inĀ  to the Message and the title in to the Subject with the recipient name so i can see if it's the CEO or not.


i triedĀ  HD_TICKET.TITLE
and
$HD_TICKET.TITLE
and
$ticknum
$owner_name

What are the variables i can use and how do i added them in ?





0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: chucksteel 7 years ago
Red Belt
0

Top Answer

Any columns selected in the select query will be available as variables for the email message. In the case of the query that JasonEgg setup he selected HD_TICKET.*, this will make all of the columns in the HD_TICKET table available, they will not be proceeded by the table name, so the TITLE column will be available as $title (variables are lower case), ID will be $id.

The query does not include the joins normally in place for owner, submitter, etc. In order to select those columns you will need to add those join statements and select the appropriate columns. For ticket submitter the join statement is:
JOIN USER SUBMITTER on USER.ID = HD_TICKET.SUBMITTER_ID

The select clause uses a comma separated list of columns, so to select the submitter's full name you would add SUBMITTER.FULL_NAME to the list after your ON_CALL column. 

The updated query should look like this:
select HD_TICKET.*,
  '5555555555@verizon.net' AS ON_CALL,SUBMITTER.FULL_NAME
from HD_TICKET 
  join HD_TICKET_CHANGE on 
    (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
     AND HD_TICKET_CHANGE.ID = <CHANGE_ID>)join USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
where 
  DAYOFWEEK(CREATED) in (1,7)
    # 1 = Sunday, 7 = Saturday
  AND TIME(CREATED) >= TIME('8:00:00')
    # at or after 8am
  AND TIME(CREATED) <= TIME('17:00:00')
    # before or at 5pm
  AND HD_TICKET_CHANGE.DESCRIPTION like 'Ticket Created%'
    # Only send email on creation of ticket


Comments:
  • I don't have a column in the USER table called SUBMITTER. And here is my Log:

    05/10/2017 13:55:10> Starting: 05/10/2017 13:55:10 05/10/2017 13:55:10> Executing Select Query... 05/10/2017 13:55:10> mysqli error: [1052: Column 'CREATED' in where clause is ambiguous] in EXECUTE("select HD_TICKET.*, '5555555555@messaging.sprintpcs.com' AS MIKE from HD_TICKET join HD_TICKET_CHANGE on (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID AND HD_TICKET_CHANGE.ID = 0) join USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID where DAYOFWEEK(CREATED) in (1,4,7) # 1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday 5, Thursday 6 = Friday, 7 = Saturday AND TIME(CREATED) >= TIME('9:00:00') # at or after 8am AND TIME(CREATED) <= TIME('17:00:00') # before or at 5pm AND HD_TICKET_CHANGE.DESCRIPTION like 'Ticket Created%' # Only send email on creation of ticket") - armasmike 7 years ago
    • When you join to a table you can alias it as another name, this allows you to join to the same table multiple times, e.g. the submitter, the owner, etc. In this case I am making an alias for the USER table called SUBMITTER to make it obvious that we are referencing the ticket submitter.

      There is also a column in the USER table named CREATED, so that is what generated the error. change the TIME(CREATED) to TIME(HD_TICKET.CREATED). - chucksteel 7 years ago
      • Look at that i learn something new Thank you works great - armasmike 7 years 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

View more:

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