/build/static/layout/Breadcrumb_cap_w.png

Pull data from one child Ticket to another child Ticket

I have a process that is for a new hire. A ticket is created and sent to the HR queue where they will put the Users Employee Number in (Located on the Custom Field Value 10) and then close the Ticket. The next ticket will need to pull the Employee Number across into the ticket. 

I have been following the following link to get the parent ticket data to populate into the child tickets.
http://www.itninja.com/question/have-k1000-use-the-same-title-for-all-tickets-in-a-process

As I am a bit rusty on my SQL, would someone be able to help me? The following is what I have thus far::

Select SQL:

SELECT
HD_TICKET.CUSTOM_FIELD_VALUE10,
HD_TICKET.CUSTOM_FIELD_VALUE14,
HD_TICKET.PARENT_ID, 
HD_QUEUE.NAME, 
HD_QUEUE.ID
FROM
HD_TICKET PARENT, HD_TICKET, HD_QUEUE
WHERE
PARENT.ID = HD_TICKET.PARENT_ID
AND NAME = HD_QUEUE.NAME
AND HD_TICKET.CUSTOM_FIELD_VALUE14 <> 1


Update SQL:

UPDATE
HD_TICKET PARENT,
HD_TICKET,
HD_QUEUE
SET
HD_TICKET.CUSTOM_FIELD_VALUE10 = HD_TICKET.CUSTOM_FIELD_VALUE10,
HD_TICKET.CUSTOM_FIELD_VALUE14 = 1
WHERE
(
(HD_TICKET.CUSTOM_FIELD_VALUE14 <> 1) 
AND (PARENT.ID = HD_TICKET.PARENT_ID) 
AND NAME = HD_QUEUE.NAME
AND NAME = 'HR'
AND HD_TICKET.ID = <TICKET_IDS>
)


0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: SquirrelHermit 9 years ago
Yellow Belt
0
ok, so I ended up figuring it out with quite a bit of trial and error. it was a combination of things, and changed alot of my origonal code. Because I was using the autoupdate check box and was setting it equal to 1 when I pulled data down from the parent, it was already equal to 1 when my HR transfer started, so it wouldnt start. I removed the setting 14 = 1 on my HR, made its order 101 and then changed the parents order in that queue to 102, and now it updates just fine.


The following is the select and update code::

Select SQL::

SELECT 
    HD_TICKET.PARENT_ID,
    HD_TICKET.CUSTOM_FIELD_VALUE10,
    HD_TICKET.ID
FROM
    HD_TICKET PARENT,
HD_TICKET
WHERE
    (
(
HD_TICKET.PARENT_ID = PARENT.ID
AND HD_TICKET.PARENT_ID <> '0'
)
AND
(
HD_TICKET.CUSTOM_FIELD_VALUE10 <> 'Desktop'
AND HD_TICKET.CUSTOM_FIELD_VALUE10 <> 'Laptop'
)

)

Update SQL:::

UPDATE
HD_TICKET HR,
HD_TICKET
Set
HD_TICKET.CUSTOM_FIELD_VALUE10 = HR.CUSTOM_FIELD_VALUE10
WHERE
(
(
HD_TICKET.PARENT_ID = HR.PARENT_ID
AND HD_TICKET.PARENT_ID <> '0'
)
AND
(
HD_TICKET.CUSTOM_FIELD_VALUE10 <> 'Desktop'
AND HD_TICKET.CUSTOM_FIELD_VALUE10 <> 'Laptop'

)
AND
(
HR.CUSTOM_FIELD_VALUE10 <> 'Desktop'
AND HR.CUSTOM_FIELD_VALUE10 <> 'Laptop'
AND HR.CUSTOM_FIELD_VALUE10 <> ''
AND HR.CUSTOM_FIELD_VALUE10 is not null
)
AND
(
HD_TICKET.CUSTOM_FIELD_VALUE1 = HR.CUSTOM_FIELD_VALUE1
AND HD_TICKET.CUSTOM_FIELD_VALUE2 = HR.CUSTOM_FIELD_VALUE2
)
AND HD_TICKET.ID = <TICKET_IDS>
AND HR.ID  < HD_TICKET.ID
                        AND HD_TICKET.CUSTOM_FIELD_VALUE14 <> 1

)

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