K1100 Ticket email based on custom Due Date
Hi.
We use our K1100 appliance for service desks but also as short-term sales activity portal. The central idea with this to track customers and follow up with the prospective customers and to do that, I created queue which contains simple information about them. I used custom field 4 for Contact Date and field 5 for Follow Up Date.
Here is where I am stuck.
My desired outcome? To have the system email the submitter of the ticket to remind them to follow up. I am not an expert in SQL so I really need some ideas as to how to make this work. I know it will have something to do with Custom field 5 and the date but don't know how to move from idea to reality.
Thanks in advance for any assistance with this.
Answers (1)
Top Answer
Assuming the "Follow Up Date" is in custom field 5 (what means it's stored in CUSTOM_FIELD_VALUE4) you could try this SELECT statement and run it daily at whatever time you want the email to be sent (the SELECT is the default SELECT from the wizard and just the last line is added) for all tickets that are not closed:
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() - unix_timestamp(HD_TICKET.TIME_OPENED)
else unix_timestamp() - 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(HD_TICKET.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 (( STATE != 'closed') and HD_TICKET.HD_QUEUE_ID = 1 )
and (STR_TO_DATE(HD_TICKET.CUSTOM_FIELD_VALUE4,'%Y-%m-%d') = Date(Now()))
You probably need to adjust the queue ID to your environment.
Comments:
-
That is excellent! I saw something similar to this in another ticket but I didn't quite get what to do with it. - jbowes 4 years ago
It depends on the due date field of a queue. Anyhow you can rename the due date field to "Follow up".
SLA can also recognize business hours and holidays in calculation. - chrpetri 4 years ago
Thanks for the suggestion which I had tried. The issue with that is the design of the built-in Due Date - it will the sales person have to do more work than they want. That is a no fly zone by the project champion...
Therefore a custom Due Date is the way around it. - jbowes 4 years ago
I will post whatever I am learning from this. - jbowes 4 years ago
get the status of the ticket
if it is not closed or is open,
and the date is today
and the queue = the queue#
Then email the submitter
I just don't understand how to structure it...
select HD_TICKET.*,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
STATE,
if(STATE != Closed or = Open
and Custom_5> = <TODAY>
and HD_TICKET.HD_QUEUE_ID = ??) - jbowes 4 years ago