How can I find the email address for a custom user field entry and use it to update another custom field?
These custom ticket fields have been created in a Queue:
A user field (HD_TICKET.CUSTOM_FIELD_VALUE39)
A text field for that user's email address (HD_TICKET.CUSTOM_FIELD_VALUE40)
A second user field (HD_TICKET.CUSTOM_FIELD_VALUE41)
A text field for the second user's email address (HD_TICKET.CUSTOM_FIELD_VALUE42)
I am trying to write a rule that will do the following, on Ticket Save, in that Queue only (HD_TICKET.HD_QUEUE_ID = 43):
If HD_TICKET.CUSTOM_FIELD_VALUE39 has an entry,
Find the email address for that user and load it into HD_TICKET.CUSTOM_FIELD_VALUE40
If HD_TICKET.CUSTOM_FIELD_VALUE41 has an entry,
Find the email address for that user and load it into HD_TICKET.CUSTOM_FIELD_VALUE42
Other rules then use these email addresses for various notifications.
Any guidance is appreciated!
Answers (2)
The rule you are looking for would be this one:
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 concat(HD_TICKET.CUSTOM_FIELD_VALUE39, HD_TICKET.CUSTOM_FIELD_VALUE41) <> ''
and HD_TICKET.HD_QUEUE_ID = 43
UPDATE:
UPDATE HD_TICKET
SET CUSTOM_FIELD_VALUE40 = CASE
WHEN CUSTOM_FIELD_VALUE39 <> '' THEN CUSTOM_FIELD_VALUE39
ELSE CUSTOM_FIELD_VALUE40
END ,
CUSTOM_FIELD_VALUE42 = CASE
WHEN CUSTOM_FIELD_VALUE41 <> '' THEN CUSTOM_FIELD_VALUE41
ELSE CUSTOM_FIELD_VALUE42
END
WHERE (HD_TICKET.ID in (<TICKET_IDS>))
Comments:
-
Thank you for your suggestion! I was receiving a syntax error, which was resolved by removing the closing ) at the end of the SELECT section. The Rule will run, but instead of returning the email address for each field, it is updating the email address fields with the user IDs (numbers) instead of the email address. - NancyC 8 years ago
-
Try this update statement:
UPDATE HD_TICKET join USER ON (HD_TICKET.CUSTOM_FIELD_VALUE39 = USER.ID or HD_TICKET.CUSTOM_FIELD_VALUE41 = USER.ID)
SET CUSTOM_FIELD_VALUE40 = CASE
WHEN CUSTOM_FIELD_VALUE39 <> '' THEN USER.EMAIL
ELSE CUSTOM_FIELD_VALUE40
END ,
CUSTOM_FIELD_VALUE42 = CASE
WHEN CUSTOM_FIELD_VALUE41 <> '' THEN USER.EMAIL
ELSE CUSTOM_FIELD_VALUE42
END
WHERE (HD_TICKET.ID in (<TICKET_IDS>)) - UntchV 8 years ago-
Getting closer! :) Now, it loads an email address:
- If both user fields are Unassigned, nothing is loaded into either email field (this is correct).
- If both user fields have an entry, the first user's email address goes into both email fields.
- If either user field has an entry (but the other is Unassigned), that user's email address goes into both fields.
Disregard my earlier correction, if you saw it... I was trying to test with the rule disabled! My first statement was correct. - NancyC 8 years ago
Top Answer
I will use a separate rule for each user/email field combination. In addition to the standard 'wizard' select code, this is what I ended up using:
Last Lines of Select SQL:
…andHD_TICKET.CUSTOM_FIELD_VALUE39 <> ''
andHD_TICKET.HD_QUEUE_ID = 43
Update SQL:
UPDATE HD_TICKET
WHERE (HD_TICKET.IDin (<TICKET_IDS>))
-------------------------------------------------------------------
I also had to add another rule for each user/email field (that runs after the rule shown above) to clear the address if the User field was changed to Unassigned:
Last Lines of Select SQL:
andCUSTOM_FIELD_VALUE39 = 0
andHD_TICKET.HD_QUEUE_ID = 43
UPDATE SQL:
UPDATE HD_TICKET
SETCUSTOM_FIELD_VALUE40 = ''
WHERE (HD_TICKET.IDin (<TICKET_IDS>))
I will use two rules for each user/email field combination. This Update code worked:
UPDATE HD_TICKET
join USER ON (HD_TICKET.CUSTOM_FIELD_VALUE39 = USER.ID)
SET CUSTOM_FIELD_VALUE40 = CASE
WHEN CUSTOM_FIELD_VALUE39 <> '' THEN USER.EMAIL
ELSE CUSTOM_FIELD_VALUE40
END
WHERE (HD_TICKET.ID in (<TICKET_IDS>)) - NancyC 8 years ago