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&#82796

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

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
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 :
and HD_TICKET.ID= xxx

Your 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
Answered 09/29/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
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
Answered 09/29/2011 by: jverbosk
Red Belt

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

Share