Rule to send an email to a group depending on a field
Hello,
I need to send an email to a email address group depending on a field in kbox, basically if the HD_TICKET.CUSTOM_FIELD_VALUE8 = 'Registers') then Send an email to the email group registers OR HD_TICKET.CUSTOM_FIELD_VALUE8 = 'Printers') then send an email to the email group Printers and so on.
I already have the rule below, but that one only sends an email to an address depending on one field:
select HD_TICKET.ID,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE,
HD_TICKET_CHANGE.COMMENT AS EMAIL_BODY,
U1.USER_NAME as OWNER_NAME,
U3.USER_NAME as LASTINPUTNAME,
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED,
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
STATE,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
U2.USER_NAME as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
U3.EMAIL as UPDATEREMAIL,
'FedEx_Labels_Group@autozone.com,kbox.support.group@autozone.com' as NEWTICKETEMAIL,
UNIX_TIMESTAMP(HD_TICKET_CHANGE.TIMESTAMP)
from ( HD_TICKET,
HD_PRIORITY,
HD_STATUS,
HD_IMPACT,
HD_CATEGORY)
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET_CHANGE.ID=<CHANGE_ID>
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
where HD_PRIORITY.ID = HD_PRIORITY_ID and
HD_STATUS.ID = HD_STATUS_ID and
HD_IMPACT.ID = HD_IMPACT_ID and
HD_CATEGORY.ID = HD_CATEGORY_ID and
HD_TICKET_CHANGE.DESCRIPTION = 'TICKET CREATED' and
HD_TICKET.TITLE = 'Used Fedex Labels Script'
Any help on this will be greatly appreciated.
I need to send an email to a email address group depending on a field in kbox, basically if the HD_TICKET.CUSTOM_FIELD_VALUE8 = 'Registers') then Send an email to the email group registers OR HD_TICKET.CUSTOM_FIELD_VALUE8 = 'Printers') then send an email to the email group Printers and so on.
I already have the rule below, but that one only sends an email to an address depending on one field:
select HD_TICKET.ID,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE,
HD_TICKET_CHANGE.COMMENT AS EMAIL_BODY,
U1.USER_NAME as OWNER_NAME,
U3.USER_NAME as LASTINPUTNAME,
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED,
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
STATE,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
U2.USER_NAME as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
U3.EMAIL as UPDATEREMAIL,
'FedEx_Labels_Group@autozone.com,kbox.support.group@autozone.com' as NEWTICKETEMAIL,
UNIX_TIMESTAMP(HD_TICKET_CHANGE.TIMESTAMP)
from ( HD_TICKET,
HD_PRIORITY,
HD_STATUS,
HD_IMPACT,
HD_CATEGORY)
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET_CHANGE.ID=<CHANGE_ID>
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
where HD_PRIORITY.ID = HD_PRIORITY_ID and
HD_STATUS.ID = HD_STATUS_ID and
HD_IMPACT.ID = HD_IMPACT_ID and
HD_CATEGORY.ID = HD_CATEGORY_ID and
HD_TICKET_CHANGE.DESCRIPTION = 'TICKET CREATED' and
HD_TICKET.TITLE = 'Used Fedex Labels Script'
Any help on this will be greatly appreciated.
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
GillySpy
14 years ago
You could break it up into multiple rules or to keep it in one rule you'll want something like this in your query:
Then address the email to the column DYNAMICGROUP
select ...
case when HD_TICKET.CUSTOM_FIELD_VALUE8='Printers' then 'printersgroup@company'
when HD_TICKET.CUSTOM_FIELD_VALUE8='Registers' then 'registersgroup@company'
else 'me@company' end DYNAMICGROUP
...from
Then address the email to the column DYNAMICGROUP
Posted by:
chris811
14 years ago
ORIGINAL: GillySpy
You could break it up into multiple rules or to keep it in one rule you'll want something like this in your query:
select ...
case when HD_TICKET.CUSTOM_FIELD_VALUE8='Printers' then 'printersgroup@company'
when HD_TICKET.CUSTOM_FIELD_VALUE8='Registers' then 'registersgroup@company'
else 'me@company' end DYNAMICGROUP
...from
Then address the email to the column DYNAMICGROUP
Thank you very much that worked great!!
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.