/build/static/layout/Breadcrumb_cap_w.png

Ticket rule change query, adding Submitter Label to custom_field_value2 help?

I've been following closely the other posts regarding the change values being added to the custom value fields, and yes, about the offset.  My issue is trying to get the submitters LABELS listed in the ticket, specifically the custom_field3 (or custom_field_value2).

 

As it stands, my SQL does a good job of finding all the tickets with submitter labels (and all the different labels that submitter has) but I can't seem to get that change query to work:

Side Note:  I've tried many variations, and will post the SQL for the tickets/labels information even further below, as it might hold something?  Thank you much.

 update HD_TICKET
left join USER ON (USER.ID = HD_TICKET.SUBMITTER_ID)
LEFT JOIN USER_LABEL_JT ON (USER_LABEL_JT.USER_ID=USER.ID)
LEFT JOIN LABEL ON (LABEL.ID = LABEL_ID)
set HD_TICKET.CUSTOM_FIELD_VALUE2 = LABEL.NAME
where
(HD_TICKET.ID in (<TICKET_IDS>))

**Individual tables template for token variable aliasing**
------------------------SELECT QUERY-----------------
select HD_TICKET.ID AS TICKNUM,
HD_TICKET.TITLE as TITLE,
HD_TICKET.HD_IMPACT_ID,
HD_TICKET.HD_PRIORITY_ID,
HD_TICKET.MODIFIED,
HD_TICKET.CREATED,
HD_TICKET.OWNER_ID,
HD_TICKET.SUBMITTER_ID,
HD_TICKET.HD_STATUS_ID,
HD_TICKET.HD_QUEUE_ID,
HD_TICKET.HD_CATEGORY_ID,
HD_TICKET.CC_LIST,
HD_TICKET.ESCALATED,
HD_TICKET.CUSTOM_FIELD_VALUE0,
HD_TICKET.CUSTOM_FIELD_VALUE1,
HD_TICKET.CUSTOM_FIELD_VALUE2,
HD_TICKET.CUSTOM_FIELD_VALUE3,
HD_TICKET.CUSTOM_FIELD_VALUE4,
HD_TICKET.CUSTOM_FIELD_VALUE5,
HD_TICKET.CUSTOM_FIELD_VALUE6,
HD_TICKET.CUSTOM_FIELD_VALUE7,
HD_TICKET.CUSTOM_FIELD_VALUE8,
HD_TICKET.CUSTOM_FIELD_VALUE9,
HD_TICKET.CUSTOM_FIELD_VALUE10,
HD_TICKET.CUSTOM_FIELD_VALUE11,
HD_TICKET.CUSTOM_FIELD_VALUE12,
HD_TICKET.CUSTOM_FIELD_VALUE13,
HD_TICKET.CUSTOM_FIELD_VALUE14,
HD_TICKET.DUE_DATE,
HD_TICKET.TIME_OPENED,
HD_TICKET.TIME_CLOSED,
HD_TICKET.TIME_STALLED,
HD_TICKET.MACHINE_ID,
HD_TICKET.SATISFACTION_RATING,
HD_TICKET.SATISFACTION_COMMENT,
HD_TICKET.RESOLUTION,
HD_TICKET.ASSET_ID,
HD_TICKET.PARENT_ID,
HD_TICKET.IS_PARENT,
HD_TICKET.APPROVER_ID,
HD_TICKET.APPROVE_STATE,
HD_TICKET.APPROVAL,
HD_TICKET.APPROVAL_NOTE,
HD_TICKET.SERVICE_TICKET_ID,
LABEL.NAME as SUBMITTER_LABEL_NAME,
HD_STATUS.NAME AS STATUS_NAME, HD_STATUS.ID,
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
JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
JOIN USER_LABEL_JT on (USER_LABEL_JT.USER_ID = U2.ID)
JOIN LABEL ON (LABEL.ID = LABEL_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 LABEL.NAME <> ''




0 Comments   [ + ] Show comments

Answers (2)

Answer Summary:
Posted by: chucksteel 11 years ago
Red Belt
1

Could you do something like this for the update query?

UPDATE HD_TICKET 
set HD_TICKET.CUSTOM_FIELD_VALUE2 = 
(SELECT GROUP_CONCAT(NAME) AS LABELS FROM ORG1.USER_LABEL_JT 
JOIN LABEL ON LABEL_ID = LABEL.ID
WHERE USER_ID = HD_TICKET.SUBMITTER_ID)
WHERE HD_TICKET.ID in <TICKET_IDS>

 


Comments:
  • Awesome! Comma separated the labels and through them all in my text custom field, awesomely done sir!


    Also, I've noticed that whenever I add that last WHERE statement that EVERY CHANGE QUERY uses, my run log freaks out. Take it off, and my change query's run great. - Wildwolfay 11 years ago
Posted by: Wildwolfay 11 years ago
Red Belt
0

 

 update HD_TICKET
JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
JOIN USER_LABEL_JT on (USER_LABEL_JT.USER_ID = U2.ID)
JOIN LABEL ON (LABEL.ID = LABEL_ID)
set HD_TICKET.CUSTOM_FIELD_VALUE2 = LABEL.NAME

seems to be working now, but can anyone advise on a way to get it to supply more than just 1 Label?

I have multiple labels per person, and my Select query recognizes this and even in MySQL it populates with the ticket per label for submitter.

 

 

 

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ