/build/static/layout/Breadcrumb_cap_w.png

Passing Data From Parent Tickets To Child Tickets.

Valid for K1 Appliances Versions 5.3.*

Hello Boxers,

If you use Processes in your Help Desk module of the k1000 then you probably experience a situation where you would have liked manually entered information from the Parent Ticket to be passed down the Child Tickets. 

An Example of this could be a Process for a New Employee. Since each employee as unique information (Names, Badge Numbers, Employee IDs and example) you would have to fill this in into each child ticket manually. However there is a way to automate this using a Custom Ticket Rule and a little bit of SQL.

We will go ahead and use the above scenario as the basis for this post so we will pull data from a parent ticket down to a child ticket in a process that is all about setting up an Employee Account. Our goal will be to pull the data from 3 custom fields down to the child ticket.

Keep in mind that when using SQL it is always best to test them out before applying them to your production environment. KACE Technical Support nor IT Ninja can be held responsible if any damage occurs from using the following SQL. 

Now let's get down to business. Here is a Screen Cap of my Custom Ticket Rule. You can Create CTRs by going to Help Desk -> Configuration -> Queue -> QUEUE_NAME_HERE -> Scroll down to buttom and click "Custom Ticket Rules:[Customize]" 

You will need to Create a CTR using the Drop Down menus but it does not matter what you use as we will be overwriting it.

Note: It is important to have a Frequency of "On Ticket Save"

 

 

Here are the queries so you can copy and paste them:

Select Query:

SELECT
PARENT.CUSTOM_FIELD_VALUE0,
HD_TICKET.TITLE,
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 Query:

UPDATE
HD_TICKET PARENT,
HD_TICKET
SET
HD_TICKET.CUSTOM_FIELD_VALUE0 = PARENT.CUSTOM_FIELD_VALUE0,
HD_TICKET.CUSTOM_FIELD_VALUE1 = PARENT.CUSTOM_FIELD_VALUE1,
HD_TICKET.CUSTOM_FIELD_VALUE2 = PARENT.CUSTOM_FIELD_VALUE2,
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>)

 

First thing you should notice is that the Update Query is using the fields CUSTOM_FIELD_VALUE followed by a number. These are the names for the custom fields in the SQL Database the K1 appliance uses. These fields will not be on by default. You can enable these fields through the specific Queue's configuration page. 

I went ahead and made these fields to be used for Name, ID, and Password for the new employee. Here is a screenshot of a blank ticket for an example.

 

We are going to use these 3 fields as the data we will want to pull from the Parent ticket down to the Child ticket; However, you can transfer the data any field possible (title, owner, priority, even a comment) if you wanted.

In my above screen shot 'Name' has the Database field name of CUSTOM_FIELD_VALUE0.

'ID' has the name of CUSTOM_FIELD_VALUE1.

'Password' has the name of CUSTOM_FIELD_VALUE2.

Here is the logic behind the Ticket Rule:

1. Look for a ticket that has as Parent Ticket and that has not yet been ran through this Ticket Rule:

WHERE

PARENT.ID = HD_TICKET.PARENT_ID     <---------- Has Parent Ticket 
AND HD_TICKET.RESOLUTION not like '%Fields Pulled From Parent Ticket%' <----------  Checks Resolution Field to see if the CTR ran for this ticket already.

2. Grab data from the fields of the parent ticket and pass them to the child

SET
HD_TICKET.CUSTOM_FIELD_VALUE0 = PARENT.CUSTOM_FIELD_VALUE0, <---------- Passes the 'Name'
HD_TICKET.CUSTOM_FIELD_VALUE1 = PARENT.CUSTOM_FIELD_VALUE1, <---------- Passes the 'ID'
HD_TICKET.CUSTOM_FIELD_VALUE2 = PARENT.CUSTOM_FIELD_VALUE2, <---------- Passes the 'Password'
HD_TICKET.RESOLUTION = CONCAT(HD_TICKET.RESOLUTION,' Fields Pulled From Parent Ticket.') <---------- Marks the ticket letting us know that it was updated by this CTR.

So any time a Parent Ticket of a process is saved it will automatically pass these fields down to the child tickets without the need to manually add them. This is extremely helpful if each child ticket is meant to be handled by different individuals or even seperate queues altogether!

Adding Data from Parent Ticket to the Child Ticket's Title.

Here is another example of a CTR to pass information from the Parent ticket down to the child ticket. The following CTR will grab the same "Name" field we used above ( CUSTOM_FIELD_VALUE0 ) and then appends it to the Title of the child ticket. So not only are we pulling data from the Parent Ticket, but we are now adding it to a completely different field of HD_TICKET.TITLE which is where the Title of the ticket is stored in the Database.

SELECT
PARENT.CUSTOM_FIELD_VALUE0,
HD_TICKET.TITLE,
HD_TICKET.ID
FROM
HD_TICKET PARENT, HD_TICKET
WHERE
PARENT.ID = HD_TICKET.PARENT_ID
AND HD_TICKET.RESOLUTION not like '% Name added to title.%'

 

UPDATE
HD_TICKET PARENT,
HD_TICKET
SET
HD_TICKET.TITLE = CONCAT(HD_TICKET.TITLE," ",PARENT.CUSTOM_FIELD_VALUE0),
HD_TICKET.RESOLUTION = CONCAT(HD_TICKET.RESOLUTION,' Name added to title.')
WHERE
((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.ID = <TICKET_IDS>)

So if we had a Parent ticket with the custome "Name" field filled with "Steven" it will then add "Steven" the Title of the Child ticket! Keep in mind this will not replace the title altogether but simply add it to the end.


Comments

  • I use parent-child ticket rules in my Helpdesk queues and they work very well. Complete write up here:

    http://www.itninja.com/blog/view/k1000-service-desk-setup-tips-things-i-have-learned

    Search on "Autopopulate Child Ticket Fields" to skip directly to the rules.

    John - jverbosk 11 years ago
  • Thanks for the writeup. One main difference in tactic that I've used is to set the default value on the custom fields to something like "Set by rule" or "Set on ticket saved" and have my select query check for that value in that field to know if the rule needs to run. - chucksteel 11 years ago
  • Thanks for the input it has help me quite a bit. I am having a hard time passing the built in category and impact fields over to the child ticket. I was thinking it should be HD_TICKET.IMPACT, and in the select query and HD_TICKET.IMPACT = PARENT.IMPACT but it is not working. Any thoughts would be greatly appreciated! - ITMando 11 years ago
  • Is there a way to have the comments from the parent ticket populate the comments section of the child tickets? - mstoyles 11 years ago
  • Is there a way to distinguish in Kbox the difference between a parent ticket of a process and a ticket that is turned into a parent ticket? I am using the copy parent ticket data to the child ticket rule and it works great for our processes. What it doesn't work well with is when we have a system outage and we open a parent ticket then multiple child tickets because the client information is not the same in these child tickets as it is in the child tickets of the process. Hope this makes sense. - scarpent 11 years ago
  • what makes these rules only run against the 1 ticket thats being saved?

    When i test the select above, it resturns several results. - brianfulcher15 7 years ago
  • This is working great for the initial creation of child tickets. How can I alter this to update child tickets whenever there is a change to the parent ticket (not only initially). For example:
    The original parent ticket title has a spelling error therefor all child tickets titles have the spelling error. I would like to correct the spelling error in the parent and pass it down to the child. This is the code i used.
    Select SQL:
    SELECT
    PARENT.TITLE,
    PARENT.CUSTOM_FIELD_VALUE6,
    PARENT.HD_CATEGORY_ID,
    HD_TICKET.TITLE,
    HD_TICKET.ID
    FROM
    HD_TICKET PARENT, HD_TICKET
    WHERE
    PARENT.ID = HD_TICKET.PARENT_ID

    UPDATE SQL
    UPDATE
    HD_TICKET PARENT,
    HD_TICKET
    SET
    PARENT.CUSTOM_FIELD_VALUE4=PARENT.TITLE,
    HD_TICKET.CUSTOM_FIELD_VALUE4 = CONCAT(PARENT.TITLE," - ",HD_TICKET.CUSTOM_FIELD_VALUE6),
    HD_TICKET.TITLE = CONCAT(PARENT.TITLE," - ",HD_TICKET.CUSTOM_FIELD_VALUE6),
    HD_TICKET.HD_CATEGORY_ID = PARENT.HD_CATEGORY_ID
    WHERE
    ((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.ID = <TICKET_IDS>)


    Thanks - splinter 6 years ago
  • This is working great for the initial creation of child tickets. How can I alter this to update child tickets whenever there is a change to the parent ticket (not only initially). For example:
    The original parent ticket title has a spelling error therefor all child tickets titles have the spelling error. I would like to correct the spelling error in the parent and pass it down to the child. This is the code i used.
    Select SQL:
    SELECT
    PARENT.TITLE,
    PARENT.CUSTOM_FIELD_VALUE6,
    PARENT.HD_CATEGORY_ID,
    HD_TICKET.TITLE,
    HD_TICKET.ID
    FROM
    HD_TICKET PARENT, HD_TICKET
    WHERE
    PARENT.ID = HD_TICKET.PARENT_ID

    UPDATE SQL
    UPDATE
    HD_TICKET PARENT,
    HD_TICKET
    SET
    PARENT.CUSTOM_FIELD_VALUE4=PARENT.TITLE,
    HD_TICKET.CUSTOM_FIELD_VALUE4 = CONCAT(PARENT.TITLE," - ",HD_TICKET.CUSTOM_FIELD_VALUE6),
    HD_TICKET.TITLE = CONCAT(PARENT.TITLE," - ",HD_TICKET.CUSTOM_FIELD_VALUE6),
    HD_TICKET.HD_CATEGORY_ID = PARENT.HD_CATEGORY_ID
    WHERE
    ((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.ID = <TICKET_IDS>)


    Thanks - splinter 6 years ago
  • Hello, is there anywhere I need to specify queue names in order to move information between queues? I created a Process that uses fields in Queue2 and creates a Child ticket in Queue1. The queues have different fields. My goal is to move the date entry (which is a custom date field for 'effective date') to Due Date in the Child ticket. I keep getting all kinds of different errors.

    SELECT PARENT.CUSTOM_FIELD_VALUE6 AS 'Effective Date'
    HD_TICKET.ID AS 'ID'
    FROM HD_TICKET PARENT, HD_TICKET
    WHERE PARENT.ID = HD_TICKET.PARENT_ID

    UPDATE HD_TICKET PARENT, HD_TICKET
    SET PARENT.CUSTOM_FIELD_VALUE6,
    WHERE (((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.TITLE NOT RLIKE '^.+ [[.-.]].*') AND HD_TICKET.ID = <TICKET_IDS>)

    Log:
    04/02/2020 13:37:48> Starting: 04/02/2020 13:37:48 04/02/2020 13:37:48> Executing Select Query... 04/02/2020 13:37:48> mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'HD_TICKET.ID AS 'ID' FROM HD_TICKET PARENT, HD_TICKET WHERE PARENT.ID = HD_TICKE' at line 2] in EXECUTE("SELECT PARENT.CUSTOM_FIELD_VALUE6 AS 'Effective Date' HD_TICKET.ID AS 'ID' FROM HD_TICKET PARENT, HD_TICKET WHERE PARENT.ID = HD_TICKET.PARENT_ID ") - rbaranowicz 3 years ago
    • I desperately need this as well.. getting similar error. - barchetta 3 years ago
This post is locked

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