Scripting

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