Ok I created a rule to auto-assign a ticket when the status is "Parts Available" and Location matches the locations each technician is in charge of. For some reason it is preventing anyone from changing ownership even if the status is "New" or something besides "Parts Available". In other words it is forcing the owner based on the location.

Here's the SQL Query:

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((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 = 'Parts Available') AND HD_TICKET.CUSTOM_FIELD_VALUE0 = 'G W Carver') OR HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Cedar Ridge') OR HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Hillcrest PDS') OR HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Indian Springs') OR HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Alternative') OR HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Credit Recovery') and HD_TICKET.HD_QUEUE_ID = 1 )
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

OK, so here's the "acting part" of the Select query, which is only selecting tickets if the STATUS.NAME column is set to "Parts Available", the GUI custom 1 field is set to a specific location value and the queue ID is set to 1:

 

and (
(((((((  HD_STATUS.NAME = 'Parts Available')
AND HD_TICKET.CUSTOM_FIELD_VALUE0 = 'G W Carver')
OR HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Cedar Ridge')
OR HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Hillcrest PDS')
OR HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Indian Springs')
OR HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Alternative')
OR HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Credit Recovery')
and HD_TICKET.HD_QUEUE_ID = 1 )

This will select the matching tickets every time this criteria is met, but won't change anything by itself.  This is what the Update query does, which raises the question - what is the corresponding Update query?

Other things it would be helpful to know:

Do you have this set to run on a schedule or on ticket save? 

If you run this Select query in MySQL Query Browser, are multiple tickets selected?

John

Answered 01/16/2013 by: jverbosk
Red Belt

  • Set to run on ticket save. When i run the above query I get multiple tickets.

    Update query:
    update HD_TICKET, USER as T5
    set HD_TICKET.OWNER_ID = T5.ID
    where T5.USER_NAME = 'ajimenez' and
    (HD_TICKET.ID in (<TICKET_IDS>))
  • I think the Select statement needs to be adjusted a bit to select for both fields in each "OR" statement. Try this adjustment at the end in place of what I pasted above:

    and (
    (HD_STATUS.NAME = 'Parts Available'
    AND HD_TICKET.CUSTOM_FIELD_VALUE0 = 'G W Carver')
    OR (HD_STATUS.NAME = 'Parts Available'
    AND HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Cedar Ridge')
    OR (HD_STATUS.NAME = 'Parts Available'
    AND HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Hillcrest PDS')
    OR (HD_STATUS.NAME = 'Parts Available'
    AND HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Indian Springs')
    OR (HD_STATUS.NAME = 'Parts Available'
    AND HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Alternative')
    OR (HD_STATUS.NAME = 'Parts Available'
    AND HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Credit Recovery')
    and HD_TICKET.HD_QUEUE_ID = 1 )

    John
  • Ahhhh... SQL must group by and statements or something. I will try it.
  • That worked! Thanks.
Please log in to comment
Answer this question or Comment on this question for clarity