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

Comments

Please log in to comment

Answers

0
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.

Answered 02/20/2015 by: chucksteel
Red Belt

  • 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")
    • Can you post your SQL statement, it looks like something might have gotten messed up in the copy and paste.
      • This content is currently hidden from public view.
        Reason: Removed by member request
        For more information, visit our FAQ's.
      • 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
      • 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
Please log in to comment
Answer this question or Comment on this question for clarity

Share