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:
Please log in to answer
Posted by:
SquirrelHermit
10 years ago
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
)