I'm trying to create a rule that sends a specific custom email (not a ticket update) to the approver when the ticket needs their approval. I have the query working, but I can't seem to target the approver email in the "Send an email for each result row option". Can the "Email Column" field include values other than these OWNER_EMAIL, SUBMITTER_EMAIL, CC_LIST. And if so, what would those values be?
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
This can be any field from your query.

The owner_email, submitter_email, cc_list are just there if you use the wizard to generate your SQL.
Answered 01/16/2012 by: dchristian
Red Belt

Please log in to comment
0
Cool. After some trial and error, I got it working. But, I'm not clear on something. If I use U3.EMAIL in the "Email Column" field, it does not work. If I use EMAIL, it works. What I don't understand is why does it correctly choose the Approver email, when it could be Owner or Submitter.

Here's my query.


SELECT HD_TICKET.ID, HD_TICKET.TITLE,U3.USER_NAME,U3.EMAIL,APPROVER_ID,APPROVAL,APPROVE_STATE
FROM
(HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
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.APPROVER_ID
LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_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.APPROVAL like '%None%') OR HD_TICKET.APPROVAL is null) AND HD_STATUS.NAME not like '%Closed%') and HD_TICKET.HD_QUEUE_ID = 1 )
Answered 01/16/2012 by: Whizzer929
Senior Yellow Belt

Please log in to comment
0
Its because the email column you choose (U3.Email) in the select is pulling from the join for the approver.

Just for grins, change U3.EMAIL to U2.EMAIL. It'll send it to the submitter.

If you did U1, the owner.
Answered 01/16/2012 by: dchristian
Red Belt

Please log in to comment
0
Got it. I assume you can only have one EMAIL field in the select? I.E. What happens if I had U2.EMAIL and U3.MAIL in the select line? Not that I would do this, just curious.
Answered 01/16/2012 by: Whizzer929
Senior Yellow Belt

Please log in to comment
0
You would have to alias them to avoid confusion.

Something like
U2.EMAIL AS EMAIL2,
U1.EMAIL AS EMAIL1
Answered 01/16/2012 by: dchristian
Red Belt

Please log in to comment
0
If you truly are just sending an email as your first post says and there is no update nor comment on the field then if you wanted to send the same rule to send an email to go to the submitter and owner and approver then you could have
select ..., STAKEHOLDERS.EMAIL as STAKEHOLDERS_EMAIL
from
(HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
left join USER STAKEHOLDERS ON STAKEHOLDERS.ID IN (OWNER_ID, SUBMITTER_ID, APPROVER_ID)
...

This query will return up to 3 rows meaning 3 emails will be sent -- one to each "stakeholder". the email column value would be STAKEHOLDERS_EMAIL

This obviously works best if you are sending the same email, but here's a way to send a different email in the same rule:
select ...
case when STAKEHOLDERS.ID=OWNER_ID then concat('Hi ',STAKEHOLDERS.FULL_NAME', you are owner')
when STAKEHOLDERS.ID=SUBMITTER_ID then concat('Hi ',STAKEHOLDERS.FULL_NAME', you are submitter')
when STAKEHOLDERS.ID=APPROVER_ID then concat('Hi ',STAKEHOLDERS.FULL_NAME', you are approver')
else 'error' end as SPECIFIC_BODY,
, STAKEHOLDERS.EMAIL as STAKEHOLDERS_EMAIL
from
(HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
left join USER STAKEHOLDERS ON STAKEHOLDERS.ID IN (OWNER_ID, SUBMITTER_ID, APPROVER_ID)
...

Now you can put $specific_body in the email and it would be different in each email.

But if your doing other things (updates, comments) and particular it's an "on ticket save" rule then it's easiest to stick to one row per select query, otherwise there are other things to keep track of I don't mention here.
Answered 01/16/2012 by: GillySpy
Seventh Degree Black Belt

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