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 ?
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:
Please log in to answer
Posted by:
chucksteel
7 years ago
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
