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 <> ''



Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

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>

 

Answered 05/03/2013 by: chucksteel
Red Belt

  • 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.
Please log in to comment

Answers

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.

 

 

 

Answered 05/02/2013 by: Wildwolfay
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share