Ticket Rule Applying to All Tickets?
Is it possible to adjust this ticket rule so that it only applies to the ticket which is currently being saved? I have specified "on Ticket Save" for the Frequency, but when a ticket is saved and the rule runs, it is being applied to all of the tickets (which is a major problem).
I borrowed the code from the Auto populating user data in custom fields forum post here:
http://itninja.com/question/auto-populating-user-data-in-custom-fields&mpage=1&key=ticket%2Crule%2Ccustom𔍬
...and came up with the following, which copies the ticket submitter's full name into the Custom Field 2. My goal is to use this field for queries, so that when users are removed from the KBOX 1100 (after leaving the company), there will still be a way to search on tickets by those users' names, as removing user accounts sets the Submitter field to Unassigned for any tickets setup for those users. <hope that was clear>
Select:
SELECT HD_TICKET.ID AS ID FROM HD_TICKET
JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
WHERE HD_TICKET.SUBMITTER_ID <>0
Update:
UPDATE HD_TICKET T
JOIN USER S ON (S.ID = T.SUBMITTER_ID)
SET T.CUSTOM_FIELD_VALUE2 = S.FULL_NAME
The copy works, but unfortunately is applying to all tickets. Unfortunately, for those tickets with no Submitter specified (i.e. those tickets with termed submitters), it is just putting 0 in the Custom Field, which undermines my goal.
Hopefully I'm just missing something obvious...
Thanks very much for your help!
John C. Verbosky
Helpdesk Coordinator
I borrowed the code from the Auto populating user data in custom fields forum post here:
http://itninja.com/question/auto-populating-user-data-in-custom-fields&mpage=1&key=ticket%2Crule%2Ccustom𔍬
...and came up with the following, which copies the ticket submitter's full name into the Custom Field 2. My goal is to use this field for queries, so that when users are removed from the KBOX 1100 (after leaving the company), there will still be a way to search on tickets by those users' names, as removing user accounts sets the Submitter field to Unassigned for any tickets setup for those users. <hope that was clear>
Select:
SELECT HD_TICKET.ID AS ID FROM HD_TICKET
JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
WHERE HD_TICKET.SUBMITTER_ID <>0
Update:
UPDATE HD_TICKET T
JOIN USER S ON (S.ID = T.SUBMITTER_ID)
SET T.CUSTOM_FIELD_VALUE2 = S.FULL_NAME
The copy works, but unfortunately is applying to all tickets. Unfortunately, for those tickets with no Submitter specified (i.e. those tickets with termed submitters), it is just putting 0 in the Custom Field, which undermines my goal.
Hopefully I'm just missing something obvious...
Thanks very much for your help!
John C. Verbosky
Helpdesk Coordinator
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
GillySpy
12 years ago
As long as the frequency = on ticket save then the select query is fine because it will automatically add this to the bottom of the query :
Your update however is not limited. You need to rewrite the update to be:
That last part will make sure that the ID (ticket number) from the select query is passed to the update
and HD_TICKET.ID= xxxYour update however is not limited. You need to rewrite the update to be:
UPDATE HD_TICKET T
JOIN USER S ON (S.ID = T.SUBMITTER_ID)
SET T.CUSTOM_FIELD_VALUE2 = S.FULL_NAME
WHERE T.ID=<TICKET_IDS>
That last part will make sure that the ID (ticket number) from the select query is passed to the update
Posted by:
jverbosk
12 years ago
Gerald,
Thanks *VERY* much for your help and for clarifying this. Everything is working now and I learned another valuable tip.
Keep up the great work!
John
Thanks *VERY* much for your help and for clarifying this. Everything is working now and I learned another valuable tip.
Keep up the great work!
John
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.