/build/static/layout/Breadcrumb_cap_w.png

Need to create child tickets when ticket is moved from a different queue

Hi,

The SQL code below creates child tickets when a new ticket is created in the queue but does not create child tickets if a ticket was moved from a different queue. Could someone assist in modifying the code below so child tickets are created when ticket is moved from a different queue or created in the same queue?

select
  HD_TICKET.ID
  ,SUBSTRING_INDEX(SUBSTRING_INDEX(HD_TICKET.CUSTOM_FIELD_VALUE7, ',', n.n), ',', -1) as 'it_need'
  ,concat(HD_TICKET.CUSTOM_FIELD_VALUE0, ", ", HD_TICKET.CUSTOM_FIELD_VALUE1, ", ", HD_TICKET.CUSTOM_FIELD_VALUE2) as employee_name
  ,HD_TICKET.CUSTOM_FIELD_VALUE4 as supervisor_name
  ,HD_TICKET.CUSTOM_FIELD_VALUE5 as supervisor_phone
  ,ifnull(SUBMITTER.USER_NAME, "Unassigned") as submitter_name
  ,CAT.NAME as category
  ,case SUBSTRING_INDEX(SUBSTRING_INDEX(HD_TICKET.CUSTOM_FIELD_VALUE7, ',', n.n), ',', -1)
    when "Hardware:Tablet" then "Hardware Group"
    when "Hardware:1 monitor" then "Hardware Group"
    when "Hardware:2 monitors" then "Hardware Group"
    when "Hardware:Oversize Monitor" then "Hardware Group"
    when "Hardware:Network Copier/Printer" then "Hardware Group"
    when "Hardware:Network Scanner" then "Hardware Group"
    when "Hardware:Desk Printer" then "Hardware Group"
    when "Network" then "Network Group"
    when "Other(s)" then "Other Group"
    when "Printer/Copier/Scanner" then "Printers Copiers Scanners Group"
    end as owner_name
  ,"tech.support" as EMAILCOLUMN
from
  HD_TICKET
  join HD_TICKET_CHANGE C on HD_TICKET.ID = HD_TICKET_ID and C.ID = <CHANGE_ID>
  cross join (select a.N + b.N * 10 + 1 n from (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) a, (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) b order by n) n
  left join USER SUBMITTER on HD_TICKET.SUBMITTER_ID = SUBMITTER.ID
  join HD_CATEGORY CAT on HD_TICKET.HD_CATEGORY_ID = CAT.ID
where
  C.DESCRIPTION like '%CREATED%'
  and n.n <= 1 + (length(HD_TICKET.CUSTOM_FIELD_VALUE7) - length(REPLACE(HD_TICKET.CUSTOM_FIELD_VALUE7, ',', '')))
  and HD_TICKET.CUSTOM_FIELD_VALUE7 != ""


Thanks,

0 Comments   [ + ] Show comments

Answers (1)

Posted by: chucksteel 6 years ago
Red Belt
0
This rule will only match tickets on creation because of this line:
C.DESCRIPTION like '%CREATED%'
You would need to change that to something like:
(C.DESCRIPTION like "%Created%" or C.DESCRIPTION like "%Changed ticket Queue%")

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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