We are trying to create a custom ticket rule that will add an email to the HD_TICKET.CC_LIST
1. We are trying to create a custom ticket rule that will add an email to the HD_TICKET.CC_LIST.
a. We have created a custom field: HD_TICKET.CUSTOM_FIELD_VALUE11 that records the full name of the person who is to be added to the CC List.
b. We have joined the USER field to the HD_TICKET table by using HD_TICKET.CUSTOM_FIELD_VALUE11 and USER.EMAIL.
2. The rule works correctly upon ticket creation.
3. We are trying to have the update script look for the USER.EMAIL in the HD_TICKET.CC_LIST and only concatenate HD_TICKET.CC_LIST when it is not found.
a. Upon updating the ticket after initial save:
i. If there is nothing in the CC_LIST field, the ticket updates correctly.
ii. If the only thing in the CC_LIST is the user from HD_TICKET.CUSTOM_FIELD_VALUE11, the value does not change (which is correct).
iii. If the CC_LIST contains any user other than the user from HD_TICKET.CUSTOM_FIELD_VALUE11, the field concatenates by adding the new user (which is correct).
iv. If the CC_LIST contains any user other than the user from HD_TICKET.CUSTOM_FIELD_VALUE11 and the user from HD_TICKET.CUSTOM_FIELD_VALUE11, the field concatenates by adding the new user again (which is incorrect).
b. It seems that the part that is not working is finding the text string USER.EMAIL within HD_TICKET.CC_LIST.
UPDATE HD_TICKET
JOIN USER ON USER.FULL_NAME = CUSTOM_FIELD_VALUE11
SET HD_TICKET.CC_LIST =
CASE WHEN HD_TICKET.CC_LIST = '' OR HD_TICKET.CC_LIST IS NULL THEN USER.EMAIL
WHEN FIND_IN_SET(USER.EMAIL, REPLACE(REPLACE(HD_TICKET.CC_LIST,',',''),' ','')) > 0 THEN HD_TICKET.CC_LIST
ELSE CONCAT(HD_TICKET.CC_LIST,', ',USER.EMAIL)
END
WHERE HD_TICKET.HD_QUEUE_ID = 12
Answers (1)
Instead of performing the logic to determine of the email should be added as part of the update statement I would do that in the select statement for the rule. Whether the email is being added to a current list or the only entry the update statement can perform the concat to add it to the list, so you really just need to find out whether the email is in the cc_list in the first place. I think that would be a simpler query on both fronts.
