Looking to create a ticket rule to email based on a custom field
I'm using a custom field to work as an "Assigned To" instead of the built in Owner field because managers are always changing and no way to keep up with a label. We are restricting Submitters with a label so not everyone sees the ticket queue. It is used by managers in our call center to identify "Colleague Improvements" identified during their work hours and so a manager can follow-up with proper procedures to correct the issue.
So I have Custom_11 set as a user lookup field named Assigned To. Now I need to create a rule that will alert when someone is assigned - create a ticket rule to email this user when this field is set to a user? Or am I over-thinking this process?
Answers (2)
I think you are over thinking it a touch as surely it is as much work to keep the label for Ticket Owners up to date as it is to keep a custom look up up to date??
But if you want to take this route I would recommend that you populate your drop down with email addresses rather than just names for the Assigned field. That way you will already have the email address available as data to use in the ticket rule.
You will also have the issue that if the ticket rule is set to run on a schedule or on ticket save you are in danger of sending multiple email alerts.
Have a ticket rule that identifies when the Assigned field is populated so !="" may work?
Then make sure you have your custom field included in the Select statement so if using CUSTOM_11
HD_TICKET.CUSTOM_FIELD_VALUE10 as MY_ASSIGNED, for example
You can then also use the MY_ASSIGNED in the "Email each recipient in query result option" so tick the box and setup the email alert.
That should start to work
In your custom ticket rule you need to add another join statement to the user table based on the custom field. If you create a rule using the wizard it will create some joins to the user table, like these:
left join USER U1 on U1.ID = HD_TICKET.OWNER_ID
left join USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
left join USER U3 on U3.ID = HD_TICKET_CHANGE.USER_ID
Since you are using a user lookup field the user ID will be stored in the custom field value, so you need to add a join like this:
left join USER AssignedTo on AssignedTo.ID = HD_TICKET.CUSTOM_FIELD_VALUE10
Note that the custom field value columns are zero based in the database, so custom field 11 is CUSTOM_FIELD_VALUE10.
With the join in place you need to also select the email column for the assigned to user, like this:
AssignedTo.EMAIL as ASSIGNED_TO_EMAIL
That column then becomes your column containing email address.
If you have problems adding these lines to your custom rule, please post your select statement.
