Update ticket based on category..... works halfway?
I am trying to update a child ticket based on the category. I want to grab certain custom values from a parent ticket and push them to a child ticket based on the category.
For example:
Parent Ticket contains computer request information for a new employee
A custom rule grabs the computer info and shoots it to the child ticket that has the "Request::Computer" category.
I have gotten this work when the category is just a single word, like "Hardware". But as soon as I change the category in the query to "Request::Computer" it selects, but does not update.
I am no SQL expert so I relied heavily on researching how exactly to do this and have gathered the following Select and Update queries. Please take a look and let me know what I'm doing wrong, or if it's even possible to do this with a two-tiered category.
Thank you!!
SELECT
PARENT.CUSTOM_FIELD_VALUE0,
HD_TICKET.TITLE,
HD_TICKET.ID,
HD_TICKET.HD_CATEGORY_ID,
HD_CATEGORY.ID,
HD_CATEGORY.NAME
FROM
HD_TICKET PARENT, HD_TICKET
JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
WHERE
(((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_CATEGORY.NAME = 'Request::Computer') AND HD_TICKET.HD_QUEUE_ID = 10)
UPDATE
HD_TICKET PARENT,
HD_TICKET,
HD_CATEGORY
SET
HD_TICKET.TITLE = CONCAT(PARENT.TITLE, ' - ', PARENT.CUSTOM_FIELD_VALUE0 ),
HD_TICKET.SUMMARY = PARENT.CUSTOM_FIELD_VALUE7,
HD_TICKET.CUSTOM_FIELD_VALUE0 = PARENT.CUSTOM_FIELD_VALUE0
WHERE
(((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.ID = ) AND HD_CATEGORY.NAME = 'Request::Computer')
Here is a snap shot of the last run log.
Last Run Log after removing HD_CATEGORY
3 Comments
[ + ] Show comments
Answers (0)
Please log in to answer
Be the first to answer this question
Either way, I clicked on "View Ticket Search Results" when the category is both "Hardware" and "Request::Computer" and it says:
"There was an error retrieving the data for this page. Please refresh the page. If the error persists, try resetting the default page layout".
The layout has never been changed
Also, when I removed HD_CATEGORY from update statement I received an error referring to an unknown column (added screenshot). - jcuellar 5 years ago
The View Ticket Search Results link won't work for rules that run on ticket save. - chucksteel 5 years ago