Custom ticket rule to get substring of HD_TICKET.TITLE
So, my title of my custom onboarding queue ticket contains the following
New Employee Enrollment * Testor Altestorosa * 2-23-2022
With the new employee name between the "*" characters, in this case "Testor Altestorosa"
I want to select only the user name from this title, into a variable that I can then have CTR automatically fill a custom field with the informaiton...
So far I have this as a select
SELECT HD_TICKET.*,
HD_TICKET.TITLE, -- $title
SUBSTRING_INDEX(TITLE, "*", -2) as Result,
'My-Emailaddress.com' AS NEWTICKETEMAIL -- $newticketemail
FROM HD_TICKET
WHERE
HD_TICKET.HD_QUEUE_ID = 22
and I have an e-mail set to send info to NEWTICKETEMAIL with the variable $result
and the result in my e-mail was "Testor Altestorosa * 2-23-2022"
so my question is how can I do this to remove the info starting with the 2nd "*" ???
any suggestions are much appreciated!
thanks
J
Answers (1)
Top Answer
Try:
SUBSTRING_INDEX(SUBSTRING_INDEX(TITLE, "*", 2), "* ", -1) as Result
Comments:
-
I was just posting an update..
I got it to work with
SUBSTRING_INDEX(SUBSTRING_INDEX(TITLE, "*", -2), "*", 1) as Result,
took a bit of testing to get it to work..
Now I have another issue.. I want to use that "Result" to insert the info into a Custom Field...
In my Update SQL I have
update
HD_TICKET
Set
HD_TICKET.CUSTOM_FIELD_VALUE43 = $result
where
HD_TICKET.ID = <TICKET_IDS>
and the field gets updated however the data in the field is "$result" not the actual text in the $result
I also tried with =Result and get an error "[1054: Unknown column 'Result' in 'field list']"
Is this even possible??
I feel it is Soooo close..
Thanks once again for any suggestions
J - jct134 2 years ago -
I just figured out that I can do the substring during the update: (now I need to configure so it only happens upon creation and only 1 time) if that is possible?
update
HD_TICKET
Set
HD_TICKET.CUSTOM_FIELD_VALUE43 = SUBSTRING_INDEX(SUBSTRING_INDEX(TITLE, "*", -2), "*", 1)
where
HD_TICKET.ID = <TICKET_IDS> - jct134 2 years ago
