Send email only once
Hello all, im new to K1000 and SQL so I am looking for some help. I have a rule that I created to send an email to a specific user when ticket status equals 'Approved' , this is working successfully however it sends an email every time the ticket gets updated and saved because the status still equals approved. How can I get it so it only sends the email once?
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
grayematter
8 years ago
In your WHERE clause, you should be able to limit to when the HD_TICKET_CHANGE.DESCRIPTION field notes a change in the approval status. Take a look at some example tickets in your database to figure out your exact requirements.
Comments:
-
I think I know what the requirements are and its that it send an email on save when the status gets changed to approved and that's working but based on the query if someone goes into the ticket and changes the description or any other field it sends another email because on ticket save the query sees the status as approved so it still meets the criteria of the query. I want it to only send when the status gets changed to approved and not when anything else gets edited. Not sure if that makes any sense. - jf578573 8 years ago
-
Filtering on whether the Approval flag is set (HD_TICKET.APPROVE_STATE or HD_TICKET.APPROVAL) is very different than checking if this ticket save is WHEN the flag is changed. That is recorded in HD_TICKET_CHANGE.DESCRIPTION or HD_TICKET_CHANGE.OWNERS_ONLY_DESCRIPTION. The field will look something like [Changed Approval from "" to "Approved"].
Make sure you are only acting on the latest change to the ticket. You should have something like the following in your query.
/* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID AND C.ID=<CHANGE_ID>
Then make sure you are limiting the selection to when the Approval is actually changing (as opposed to whether or not the field is set). Your WHERE clause should have something like the following.
((HD_TICKET_CHANGE.DESCRIPTION like '%Changed Approval from "" to "Approved"%') or (HD_TICKET_CHANGE.OWNERS_ONLY_DESCRIPTION like '%Changed Approval from "" to "Approved"%')) - grayematter 8 years ago-
Yup totally don't have any of those things. Here is what I do have.
select HD_TICKET.*,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
STATE,
if(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,
if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
case upper(STATE)
when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
Q.NAME as QUEUE_NAME
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID
and HD_STATUS.ID = HD_STATUS_ID
and HD_IMPACT.ID = HD_IMPACT_ID
and HD_CATEGORY.ID = HD_CATEGORY_ID
and (( HD_STATUS.NAME = 'Approved') and HD_TICKET.HD_QUEUE_ID = 3 ) - jf578573 8 years ago -
Give this a try:
select HD_TICKET.*,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
STATE,
if(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,
if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
case upper(STATE)
when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
Q.NAME as QUEUE_NAME
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
/* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID AND C.ID=<CHANGE_ID>
LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID
and HD_STATUS.ID = HD_STATUS_ID
and HD_IMPACT.ID = HD_IMPACT_ID
and HD_CATEGORY.ID = HD_CATEGORY_ID
and (( HD_STATUS.NAME = 'Approved') and HD_TICKET.HD_QUEUE_ID = 3 )
and ((HD_TICKET_CHANGE.DESCRIPTION like '%Changed Approval from "" to "Approved"%') or (HD_TICKET_CHANGE.OWNERS_ONLY_DESCRIPTION like '%Changed Approval from "" to "Approved"%')) - grayematter 8 years ago -
Awesome. Unfortunately I just gave it a try and it errored out with the following log entry.
03/10/2016 14:35:43> Starting: 03/10/2016 14:35:43 03/10/2016 14:35:43> Executing Select Query... 03/10/2016 14:35:43> mysqli error: [1054: Unknown column 'HD_TICKET_CHANGE.DESCRIPTION' in 'where clause'] in EXECUTE("select HD_TICKET.*, HD_STATUS.NAME AS STATUS_NAME, HD_STATUS.ORDINAL as STATUS_ORDINAL, HD_IMPACT.ORDINAL as IMPACT_ORDINAL, HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL, HD_PRIORITY.ORDINAL as PRIORITY_NUMBER, STATE, if(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME, if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS, if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED, if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED, if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED, if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED, if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED, if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED, if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE, case upper(STATE) when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED) when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED) else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE, if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME, U1.FULL_NAME as OWNER_FULLNAME, U1.EMAIL as OWNER_EMAIL, if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME, if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME, U2.FULL_NAME as SUBMITTER_FULLNAME, U2.EMAIL as SUBMITTER_EMAIL, if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME, if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME, if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS, Q.NAME as QUEUE_NAME from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY) /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID AND C.ID=11368 LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID where HD_PRIORITY.ID = HD_PRIORITY_ID and HD_STATUS.ID = HD_STATUS_ID and HD_IMPACT.ID = HD_IMPACT_ID and HD_CATEGORY.ID = HD_CATEGORY_ID and (( HD_STATUS.NAME = 'Approved') and HD_TICKET.HD_QUEUE_ID = 3 ) and ((HD_TICKET_CHANGE.DESCRIPTION like '%Changed Approval from "" to "Approved"%') or (HD_TICKET_CHANGE.OWNERS_ONLY_DESCRIPTION like '%Changed Approval from "" to "Approved"%')) and (HD_TICKET.ID = 7119) ") - jf578573 8 years ago -
Copy/Paste, my frenemy. Try changing this line
/* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID AND C.ID=<CHANGE_ID>
to this
/* latest change ***/ JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID AND HD_TICKET_CHANGE.ID=<CHANGE_ID> - grayematter 8 years ago -
Sadly no-good. The query ran with no errors this time but found 0 rows to send an email to. When I hit view results I see this error.
There were syntax errors in your query.
mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID LEFT JOIN USER U2' at line 33] in EXECUTE("select HD_TICKET.*, HD_STATUS.NAME AS STATUS_NAME, HD_STATUS.ORDINAL as STATUS_ORDINAL, HD_IMPACT.ORDINAL as IMPACT_ORDINAL, HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL, HD_PRIORITY.ORDINAL as PRIORITY_NUMBER, STATE, if(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME, if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS, if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED, if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED, if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED, if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED, if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED, if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED, if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE, case upper(STATE) when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED) when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED) else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE, if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME, U1.FULL_NAME as OWNER_FULLNAME, U1.EMAIL as OWNER_EMAIL, if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME, if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME, U2.FULL_NAME as SUBMITTER_FULLNAME, U2.EMAIL as SUBMITTER_EMAIL, if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME, if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME, if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS, Q.NAME as QUEUE_NAME from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY) /* latest change ***/ JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID AND HD_TICKET_CHANGE.ID= LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID where HD_PRIORITY.ID = HD_PRIORITY_ID and HD_STATUS.ID = HD_STATUS_ID and HD_IMPACT.ID = HD_IMPACT_ID and HD_CATEGORY.ID = HD_CATEGORY_ID and (( HD_STATUS.NAME = 'Approved') and HD_TICKET.HD_QUEUE_ID = 3 ) and ((HD_TICKET_CHANGE.DESCRIPTION like '%Changed Approval from "" to "Approved"%') or (HD_TICKET_CHANGE.OWNERS_ONLY_DESCRIPTION like '%Changed Approval from "" to "Approved"%')) and HD_TICKET.HD_QUEUE_ID = '3' ") - jf578573 8 years ago -
Not sure if this matters or not but the way I was getting it to send email originally was I have the 'Email each recipient in query results' checked and the 'column containing email address' field has 'OWNER_EMAIL' variable - jf578573 8 years ago
-
The OWNER_EMAIL bit should be correct. If you are trying this in a query editor like MySQL Workbench, you'll need to supply an appropriate change ID from the HD_TICKET_CHANGE table. The <CHANGE_ID> is automatically replaced with the current change ID on ticket save. - grayematter 8 years ago
-
No its straight in the K1000 that im running these - jf578573 8 years ago
-
To get any results from the interface, you'll need to change the "<CHANGE_ID>" to a sample change ID from the database. Since clicking the "Run Now" button does not pass a change ID, the query will return no results.
The other way to test is to create a dummy ticket and go through the motions of your approval procedure. - grayematter 8 years ago -
Not sure I am following you on that - jf578573 8 years ago
