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

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

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

)

Answered 09/22/2014 by: SquirrelHermit
Senior White Belt

Please log in to comment
Answer this question or Comment on this question for clarity