/build/static/layout/Breadcrumb_cap_w.png

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


0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: IgorAngelini 2 years ago
Second Degree Blue Belt
0

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
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ