How Do I Pass Custom Fields From a Child Ticket to a Parent Ticket in KACE?
I know this question has already been asked and answered a few times before only in reverse (i.e. Parent to Child). I specifically need to know the SQL commands for passing info from the Child to the Parent. In other words, I need the Owner of the Child to fill-in three custom fields and then have that information auto-populate into the corresponding three fields on the Parent ticket. Of course this should happen on save of the Child ticket.
Answers (2)
The rules are very similar to updating the child based on the parent, you just need to change a couple of values in the update statement:
UPDATE HD_TICKET PARENT, HD_TICKET SET PARENT.CUSTOM_FIELD_VALUE3 = HD_TICKET.CUSTOM_FIELD_VALUE3 WHERE ((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.ID = <TICKET_IDS>)
This worked for me in pretty limited testing.
Comments:
-
Thanks Chuck. I'll give that a try and update here. - svargas 11 years ago
It looks like I finally got this working. Apparently, KACE will append the actual ticket id to the where clause of the select statement for “on Ticket Save” rules. So, the select query ended up being a lot simpler than what I had thought originally.
Here is the final version that I used.
SELECT
HD_TICKET.ID,
HD_TICKET.PARENT_ID
FROM
HD_TICKET
WHERE
HD_TICKET.CREATED < NOW()
UPDATE
HD_TICKET PARENT,
HD_TICKET
SET
PARENT.CUSTOM_FIELD_VALUE2 = HD_TICKET.CUSTOM_FIELD_VALUE0,
PARENT.CUSTOM_FIELD_VALUE3 = HD_TICKET.CUSTOM_FIELD_VALUE1,
PARENT.CUSTOM_FIELD_VALUE4 = HD_TICKET.CUSTOM_FIELD_VALUE2
WHERE
PARENT.ID = HD_TICKET.PARENT_ID
AND HD_TICKET.ID = <TICKET_IDS>
Comments:
-
I'd recommend that you make your select statement a little more selective. For instance, you may want to make sure it only matches child tickets (where HD_TICKET.PARENT_ID = 0) and perhaps check to see if the custom fields aren't still the default values. - chucksteel 11 years ago