ITNinja

Stubox,

When you create a custom view a record is created in HD_TICKET_FILTER.

What i did is create a custom view called "Tickets Created Today".

As a criteria i used create date contains and i entered "2010-11-11" (without the quotes).

I purposely entered yesterdays date. Trust me there's a method to my madness.

When i did that, this was created in the HD_TICKET_FILTER.FIND_FIELDS
a:7:{s:7:"WFIELD5";s:17:"HD_TICKET.CREATED";s:11:"EXP_SELECT5";s:8:"CONTAINS";s:6:"INPUT5";s:10:"2010-11-11";s:13:"UNION_SELECT6";s:1:"0";s:13:"UNION_SELECT7";s:1:"0";s:13:"UNION_SELECT8";s:1:"0";s:11:"FILTER_NAME";s:21:"Tickets Created Today";}

Next I created a help desk run that would run every morning at 12:05 AM.

Here is my select statement:
SELECT 1 FROM DUAL

Here is my update:
UPDATE HD_TICKET_FILTER
SET FIND_FIELDS = REPLACE(FIND_FIELDS,DATE(DATE_SUB(SYSDATE(),INTERVAL 1 DAY)),DATE(SYSDATE()))
WHERE NAME = 'TICKETS CREATED TODAY'

What the update statement does is parse through the FIND_FIELDS column looking for yesterday's date.

When it finds it, it replaces yesterdays date with todays.

After setting up the rule I did a run once.

This updated yesterdays date (which i entered when i created the view) with todays.

Going forward since it runs everyday at 12:05 am i should have a rolling dynamic view.

WARNING:
Since this runs everyday I am unable to test if this is fully working. I wont be sure for another couple of days.

DO NOT IMPLEMENT THIS INTO PRODUCTION WITHOUT TESTING FOR A FEW DAYS!!!!

Also remeber that views are USER specific. Each user that will want this view will need to setup his/her own "Tickets Created Today" View using yesterday's date.

When this is done, the rule will need to be run once to catch all the new created views.

What you've suggested should work. The following worked for me. However, it appears you need to specify the table for CREATED in your WHERE clause - it is ambiguous if more than one table has the same field (USER and HD_TICKET both have a CREATED field).

SELECT T.ID AS 'Ticket #', T.TITLE AS 'Issue', U.FULL_NAME AS 'Owner', S.NAME AS 'Status' FROM HD_TICKET T
JOIN USER U ON (T.OWNER_ID = U.ID)
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
JOIN HD_QUEUE Q ON T.HD_QUEUE_ID=Q.ID and Q.NAME ='Queue Name'
WHERE DATEDIFF(NOW(), T.CREATED) < 1
ORDER BY T.ID


That works great, thanks!

Is it possible to have a custom view (in the Helpdesk) that lists all tickets created today?

Is there a TODAY or Last Month sort of wildcard?

Hey just wanted to give everyone an update.

This has been working in the test environment for the past week.

Hi dchristian

I thought I had replied to your suggestion but clearly didn't! Sorry for that.

Many thanks for input it should be very useful.

Cheers,

StuBox

Not sure if this would be asking too much but is it possible to create a custom view that displays tickets for the current week? Current week being Monday to Sunday for that week.

Or maybe it would be easier to call it a custom view for the last 7 days (last 7 days including today). So similar to your rule above, the date would be today's date minus 7 days.