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!)

7 Comments   [ + ] Show Comments

Comments

  • Regarding the category you'll have to perform the matching on the category name and not the category ID. You can't just set the category ID on the child ticket to that of the parent because categories are queue specific.

    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).
  • Thanks for the answer chucksteel but I'm afraid I don't quite follow. Again, I'm very new to SQL.

    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.
  • Can you post the select and update statements for your rule?
  • Sure. This is the one that works.

    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>)
  • Do you have a different rue for setting the category? I'd like to see the statements for that.
  • No, I was trying to do it all in one rule. Is that what I'm doing wrong? Do I need do do them in separate rules?
    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.
    • The categories work differently enough that I would recommend having a separate rule to set it.
  • Thanks. That's good to know. I tried that; took my original CTR that worked, removed what I thought I didn't need and added info for category.

    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>)
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity