Custom ticket rule to auto populate labels assigned to the submitter's machine into a custom field in a ticket
Hi,
I have been trying to figure out a way to auto populate all labels assigned to the ticket submitter's machine. I am new to sql and I just know basic operations. My idea is to do something like this:
select ID, submitter_ID, MACHINE_ID, CUSTOM_FIELD_VALUE0 from HD_TICKET where HD_QUEUE_ID=19
Then for every ID in that table I want to edit the value of CUSTOM_FIELD_VALUE0 to show the Labels assigned to that MACHINE_D .. something like this: select NAME from LABEL where LABEL.ID=(select LABEL_ID from MACHINE_LABEL_JT where MACHINE_ID=HD_TICKET.MACHINE_ID)
.. but i still cannot get it to work, bear in mind that there might be more than 1 label attached to 1 MACHINE_ID.
Any help is much appreciated.
Answers (1)
Here's a Select query to get you started. You can change the separator in the GROUP_CONCAT statement to whatever you like, but I'd be interested in seeing if the newline character would make it populate a multi-select field.
SELECT HD_TICKET.ID AS TICKET, USER.USER_NAME, MACHINE.NAME AS MACHINES,
GROUP_CONCAT(DISTINCT LABEL.NAME ORDER BY 1 SEPARATOR '\n') AS LABELS
FROM MACHINE
JOIN MACHINE_LABEL_JT ON (MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID)
JOIN LABEL ON (MACHINE_LABEL_JT.LABEL_ID = LABEL.ID)
JOIN HD_TICKET ON (HD_TICKET.MACHINE_ID = MACHINE.ID)
JOIN USER ON (USER.ID = HD_TICKET.SUBMITTER_ID)
GROUP BY MACHINES
You might also consider just using this query in the custom field itself, but would obviously want to results limited to just the ticket you are working on (which is where a ticket rule would probably make more sense).
John
