/build/static/layout/Breadcrumb_cap_w.png
11/09/2016 1051 views
Is it possible to show other users ticket information?

I have the following case - The admins create a ticket for a specific process. This ticket can only create by admins. I want that other users (from management) can take a look at the ticket (not to change, only to look at it). The user that look at the ticket is choosen in the ticket. Can this be implemented?

Hope for help, thanks!
Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

2
I've found a way..

I created a field wit following settings:

"Layout Ticket Fields"
Custom_8
New Submitter
Not RequiredOwners Only - Hidden from Users
"Custom Fields"
CUSTOM_8Single Selectquery:SELECT FULL_NAME FROM ORG1.USER WHERE FULL_NAME not like "admin%" AND FULL_NAME like "% %" ORDER BY FULL_NAME ASC

New Ticket Rule:

SQL-Select:
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(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_TICKET.CUSTOM_FIELD_VALUE7 != '') AND HD_STATUS.NAME != 'Closed') and HD_TICKET.HD_QUEUE_ID = 15 )

SQL-Update:
UPDATE HD_TICKET 
JOIN HD_TICKET as TEST on TEST.ID = HD_TICKET.ID
SET HD_TICKET.SUBMITTER_ID =
(
SELECT ID FROM ORG1.USER WHERE USER.FULL_NAME = TEST.CUSTOM_FIELD_VALUE7
)
WHERE
HD_TICKET.ID = (<TICKET_IDS>)

Frequency:
"on Ticket Save"

Answered 11/09/2016 by: svmay
Red Belt

  • Why are 2 rows updated in my "last run log"?

    11/09/2016 10:40:29> Starting: 11/09/2016 10:40:29 11/09/2016 10:40:29> Executing Select Query... 11/09/2016 10:40:29> selected 1 rows 11/09/2016 10:40:29> Executing Update Query... 11/09/2016 10:40:29> updated 2 rows 11/09/2016 10:40:29> Ending: 11/09/2016 10:40:29
    • Found the error. I edit the sql-update!