is it possible to use a rule to change the submitter?
we have a web form that generates an email with info to populate the ticket using email tokens.all the emails come from the same email address and that is the one it sees as submitter. the submitter field is the only one that I cannot change using tokens.
we have custom fields for first name last name email address. how do i take the info from them and change the submitter field to the name and email address from the custom fields.
any help would be appreciated.
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
Since you're using custom rules it is difficult to give explicit code. In the following I'm assuming the the user's email address is in the first custom field, which correlates to the CUSTOM_FIELD_VALUE0 column in the HD_TICKET table. If the user's email address is in another custom field, then the column will be different, e.g. custom field 6 correlates to CUSTOM_FIELD_VALUE5. It's confusing that the developers made the SQL column names zero based instead of one based, but that's life.

The first thing will be to craft a select statement that will only act on the tickets that you need to set the submitter. That may be something along the lines of:
SELECT HD_TICKET.ID
FROM HD_TICKET
left join USER SUBMITTERUSER on SUBMITTERUSER.ID = HD_TICKET.SUBMITTER_ID
WHERE SUBMITTERUSER.EMAIL != CUSTOM_FIELD_VALUE0

This would select tickets where the submitter's email address doesn't match the email address in the first custom field. If you aren't always going to change the submitter based on this difference, then you'll need to add another trigger for when the rule should fire.

The update statement then needs to change the submitter column appropriately, something like this should work:
UPDATE HD_TICKET
SET HD_TICKET.SUBMITTER_ID =
(SELECT USER.ID FROM USER WHERE USER.EMAIL = HD_TICKET.CUSTOM_FIELD_VALUE0)
WHERE (HD_TICKET.ID in (<TICKET_IDS>))

This updates the submitter_id column to the result of the sub select statement that searches the user table for the user id mathcing the email address in the first custom field. If the rule is set to run on ticket save, then it should only match one ticket so you could change the where clause, but this will always work.
Answered 11/10/2011 by: steelc
Senior Yellow Belt

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