Ticket Rule that replaces the Ticket Submitter
I am trying to create a Ticket Rule which will read the subject line of a newly submitted ticket, pull an email address from the subject line, and then replace the Ticket Submitter field with the new email address.
There are keywords in the subject line that border the email address I want to pull. Example: |Submitted by:firstname.lastname@example.org
I have the following SQL:
update HD_TICKET, USER as T5
set HD_TICKET.SUBMITTER_ID = SUBSTRING(HD_TICKET.TITLE, CHARINDEX('|Submitted by:', HD_TICKET.TITLE)+LEN('|Submitted by:'), LEN(HD_TICKET.TITLE))
HD_TICKET.ID in (<TICKET_IDS>)
However, I'm receiving the following error: mysqli error: [1370: execute command denied to user 'USER1' for routine 'ORG1.CHARINDEX'] in EXECUTE("update HD_TICKET, USER as T5 set HD_TICKET.SUBMITTER_ID = SUBSTRING(HD_TICKET.TITLE, CHARINDEX('|Submitted by:', HD_TICKET.TITLE)+LEN('|Submitted by:'), LEN(HD_TICKET.TITLE)) where HD_TICKET.ID in (16255)")
I'm not sure what I'm doing wrong. Any tips?
It look as if you are trying to set the SUBMITTER_ID with something other than a numerical value?? SO that is probably why KACE is complaining. The HD_TICKET.SUBMITTER_ID file needs to have the ID of the submitter which is linked from the ID in the USER table. So if you have the email address you need to go to the USER table to retrieve the correct USER.ID for that account and insert that numerical value i.e. '63' into the HD_TICKET.SUBMITTER_ID field.
Im curious as to why you want to do this. I may steal this idea... we have a business case for executive admins placing tickets for vip's that this might work for. Only problem is, the EA wants copies of the emails going forward.. and we dont have CC on because it was a nightmare. Maybe can store the original submitter email in a custom field and then use it to cc. hmmmm