rule to add submitter
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.
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
Answers (1)
Please log in to answer
Posted by:
steelc
12 years ago
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.
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.
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.
