/build/static/layout/Breadcrumb_cap_w.png

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
  • When is your rule set to run? Are there errors in the last run log of the rule? - chucksteel 6 years ago
    • It is set to run at save. There are no errors, I have added a snapshot of the last run log. - jcuellar 6 years ago
  • I would recommend turning on the option to email results and have the results sent to you. That will verify that the select query is returning the correct data. Also, you don't need to include HD_CATEGORY in your update statement, because you aren't changing it, you have the ticket ID in the <TICKET_IDS> variable, so you only need to get the information from the parent ticket. That will help simplify the update query a little bit. - chucksteel 6 years ago
    • I selected the option to email, but it never sent anything. I'm guessing because email hasn't been configured for Kace.... I don't have access to add it either, so I'm just waiting.

      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 6 years ago
      • You need to also remove the AND HD_CATEGORY.NAME = 'Request::Computer' from the update statement.

        The View Ticket Search Results link won't work for rules that run on ticket save. - chucksteel 6 years ago
  • Okay, I cleaned it up. I noticed in the previous last run log error I posted, it says something about trying to update ticketID 50. Well there hasn't even been 50 tickets created in our whole Service Desk. Not sure if that helps narrow it down any. - jcuellar 6 years ago

Answers (0)

Be the first to answer this question

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