A ticket rule to CREATE a ticket based on other criteria....
I've read through the FAQ for creating a child ticket from a parent ticket, but it seems very... bland. Also, Having to make it send emails really cuts down on what I want it to do, and what filters.
For example: I want it to read my custom value fields and say HEY this one is checked, (Email Login Only = 1 basically) and if that is true, and a ticket is there, then I want it to create a child ticket that titles "Email Login Setup"
If the checkbox is NOT marked (<>1 ) then it does NOT find the ticket thus it does NOTHING. This works great for filtering out my parent ticket (is_parent = '1'). And I've got multiple rules set up to read the checkmarks individually. Now that I have the SQL setup and the filters are working great, how do I tell the system to create a ticket when this parent ticket is made/saved.
Basically: New Hire process has check boxes, if EMAIL checkbox is marked I want to create a child ticket that is titles CREATE EMAIL. If the checkbox is NOT marked, i do not want this ticket created.
Another approach, if I can't force ticket creation on certain areas, do I just make a ton of step 1 process child tickets, and have a rule that says "if the name = this child ticket, but the box is NOT marked on the parent ticket, set to unassigned and close immediately" ??? What is the best way to go about generating tickets based on info from the Parent ticket?? And to pass that information on???
Filter for one of my parent tickets is as follows:
select HD_TICKET.ID AS TICKNUM,
HD_TICKET.TITLE as TITLE,
HD_STATUS.NAME AS STATUS_NAME, HD_STATUS.ID,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
Q.NAME as QUEUE_NAME,
'[email@example.com]firstname.lastname@example.org[/email], email@example.com' as KURTISEMAIL
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID
and HD_STATUS.ID = HD_STATUS_ID
and HD_IMPACT.ID = HD_IMPACT_ID
and HD_CATEGORY.ID = HD_CATEGORY_ID
and is_parent = '1'
and HD_TICKET.CUSTOM_FIELD_VALUE1 = '1'
and HD_TICKET.CUSTOM_FIELD_VALUE2 <> '1'
and HD_TICKET.HD_QUEUE_ID = 10