K1000: Pass category information from Parent to Child ticket
In working in a process, I'd like to use my ticket rule to copy the category from my Parent ticket in one queue to my child ticket that's in a different queue. The category field in the parent only has 3 choices, but there are over 100 in the child (including the same 3 from the parent).
I'd also like to copy what I'm calling the "Requester" (Submitter) from the parent to the child. This may prove more difficult as it could come from one of two fields--"Requester" or "Requester--Other".
Is one or both even possible?
With a lot of help from this forum and a coworker, I've been able to get the rule to copy the title and a few custom fields, but no luck with the category or requester. (I'm brand new to KACE & SQL and you all here at ITNINJA have been invaluable to me. I've built 3 queues from scratch by myself in a few weeks time and I couldn't have done it without site. I can't thank you enough!)
Answers (0)
Be the first to answer this question
Setting the submitter should be just like setting any of the other fields, though (except for your case where you might need to select the other field). - chucksteel 10 years ago
I added the following to the select query: PARENT.CATEGORY.NAME,
HD_CATEGORY.NAME,
and to the update query: HD_CATEGORY.NAME = PARENT.CATEGORY.NAME,
Received 1054 error for Parent. Everything else in the rule is correct b/c it worked beforehand.
I also tried variations: removed .NAME from Parent, then both. None worked. 1054 error for Parent on all. I ran a report on both the parent and the child queues to be sure I had the correct field names before writing the rule. - celestialpickles 10 years ago
SELECT
PARENT.TITLE,
HD_TICKET.TITLE,
PARENT.CUSTOM_FIELD_VALUE0,
HD_TICKET.CUSTOM_FIELD_VALUE12,
PARENT.CUSTOM_FIELD_VALUE4,
HD_TICKET.CUSTOM_FIELD_VALUE13,
HD_TICKET.ID
FROM
HD_TICKET PARENT,
HD_TICKET
WHERE
PARENT.ID = HD_TICKET.PARENT_ID
and HD_TICKET.RESOLUTION not like '%Fields Pulled from Parent Ticket%'
UPDATE
HD_TICKET PARENT,
HD_TICKET
SET
HD_TICKET.TITLE = PARENT.TITLE,
HD_TICKET.CUSTOM_FIELD_VALUE12 = PARENT.CUSTOM_FIELD_VALUE0,
HD_TICKET.CUSTOM_FIELD_VALUE13 = PARENT.CUSTOM_FIELD_VALUE4,
HD_TICKET.RESOLUTION=CONCAT(HD_TICKET.RESOLUTION,'Fields Pulled from Parent Ticket')
WHERE
((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.ID=<TICKET_IDS>) - celestialpickles 10 years ago
Since I was able to copy over a few different fields with this one rule, I'd hoped to be able to do it all together. - celestialpickles 10 years ago
I got a 1054 error for Parent_Category.
Here's the code:
SELECT
PARENT.CATEGORY.NAME,
HD_CATEGORY.NAME,
HD_TICKET.ID
FROM
HD_TICKET PARENT,
HD_TICKET
WHERE
PARENT.ID = HD_TICKET.PARENT_ID
and HD_TICKET.RESOLUTION not like '%Information pulled from Parent ticket%'
UPDATE
HD_TICKET PARENT,
HD_TICKET
SET
HD_CATEGORY.NAME = PARENT.CATEGORY.NAME,
HD_TICKET.RESOLUTION=CONCAT(HD_TICKET.RESOLUTION,'Information pulled from Parent ticket')
WHERE
((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.ID=<TICKET_IDS>) - celestialpickles 10 years ago