How do I set up a due date with notification.
We have several compliance related task that I need to assign, I want to be able to assign a due date to the task (ticket) and have the K1000 notify the assigned individual of the upcoming due date for this task. Any assistance with this would be greatly appreciated, thank you for your time...JB
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
9 years ago
There are a couple of ways you can implement this. In our environment we chose to use a scheduled report. This SQL report finds tickets in queue 13 that are open and due today:
FROM HD_TICKET
JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID)
LEFT JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
WHERE (HD_TICKET.HD_QUEUE_ID = 13) AND ((DATE(HD_TICKET.DUE_DATE) = DATE(NOW()))) AND HD_STATUS.STATE != 'closed'
GROUP BY HD_TICKET.ID ORDER BY ID
Note that if your queue has a different ID you will need to change the HD_TICKET.HD_QUEUE_ID = 13 value to match.
You can create this report by going to Reporting, Choose Action, New (SQL). Once the report is created click on Report Schedules and add a new schedule to have the report emailed to the folks that need to be informed.
Comments:
-
Thank you Chuck, I changed the queue number to the one I need, I am getting a mysql error when trying to save the sql as a report:
mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM HD_TICKET JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) LE' at line 1] in EXECUTE( "FROM HD_TICKET JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID) LEFT JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID) WHERE (HD_TICKET.HD_QUEUE_ID = 7) AND ((DATE(HD_TICKET.DUE_DATE) = DATE(NOW()))) AND HD_STATUS.STATE != 'closed' GROUP BY HD_TICKET.ID ORDER BY ID LIMIT 0") - jbaroni 9 years ago-
Can you post your SQL statement, it looks like something might have gotten messed up in the copy and paste. - chucksteel 9 years ago
-
Chuck, I was able to fix it. Here is what I used, thanks again for your help...JB
SELECT HD_TICKET.ID, HD_STATUS.ID, HD_TICKET.SUBMITTER_ID, HD_CATEGORY.ID, HD_TICKET.OWNER_ID, HD_PRIORITY.ID
FROM HD_TICKET
JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID)
LEFT JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
WHERE (HD_TICKET.HD_QUEUE_ID = 7) AND ((DATE(HD_TICKET.DUE_DATE) = DATE(NOW()))) AND HD_STATUS.STATE != 'closed'
GROUP BY HD_TICKET.ID - jbaroni 9 years ago -
Thanks again Chuck, I was able to fix it using the following statement:
SELECT HD_TICKET.ID, HD_STATUS.ID, HD_TICKET.SUBMITTER_ID, HD_CATEGORY.ID, HD_TICKET.OWNER_ID, HD_PRIORITY.ID
FROM HD_TICKET
JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID)
LEFT JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
WHERE (HD_TICKET.HD_QUEUE_ID = 7) AND ((DATE(HD_TICKET.DUE_DATE) = DATE(NOW()))) AND HD_STATUS.STATE != 'closed'
GROUP BY HD_TICKET.ID - jbaroni 9 years ago
