Sending Conditional Emails based on a Type field.

Report · Dec 13, 2012

What is the best solution to send e-mails to a list of recipients for only certain Ticket Types?

- I've already created a table/form that connects each User/Email to the Ticket Types (see image below)

- My problem is how to create the Query that will "filter" and create the list of email address?

- Every new ticket gets assigned a TicketType..

- Every user has been setup ahead of time with the appropriate types checked..

- But each user only wants their emails and NOT any unchecked types?

- Here is my idea of a tblEmailAction that indicates which Ticket Types a recipient is interested in and then it saves the ID of that ticket type into the table. Now just need to match up the values in the original ticket and generate the email list..?

Report · Dec 13, 2012

The text on your screen shot can't be read. However, the crux of your problem seems to be identifying who should get what email.

You say that each new ticket gets assingned a ticket type. Does it also get assigned to a user? If so, you need to find the applicable tables and fields. If not, what you are attempting can't be done.

Report · Dec 13, 2012

If you click the picture it should open in full, it does when I click it..

Here is the normal of flow..

1) New ticket is Created and always assigned one of the Ticket Types (see image above)

-Email is sent out: "New Ticket# 1001 has been created"

2) Ticket is Accepted by a User

-Email is sent out: "Ticket# 1001 has been accepted by Joe_User"

3) Ticket is Updated periodically

-Email is sent out: "Ticket# 1001 has been updated"

4) Ticket is eventually Closed

-Email is sent out: "Ticket# 1001 has been closed by Joe_User"

Currently I have the emails going out to every user at each step.. but wanting to send "conditional" emails depending on the Ticket Type of the Ticket.

If I create the following query I do get just the users, that have that Ticket Type, for that Ticket#..

but not sure how to expand this query to allow for all Cases of Types?

(this ticket has ADSL as the Ticket Type)

Report · Dec 13, 2012

This forum has clickable images? Who knew?

In any event, step 2 - Ticket is accepted by user. What happens on the database during this step?

Report · Dec 13, 2012

When a ticket is Accepted, the field called ttOwner in the tblTickets is updated to that "Logged-In Users Name"

- Before the ticket is accpeted --> ttOwner = "Unassigned"

- After the ticket is accepted --> ttOwner = "Joe_User"

The query for Accepted emails is working ok:

---------------------------------------------------

SELECT tblusers.email, tbldept.dpDesc, tblemailaction.uea_Accepted

FROM tblusers, tbldept, tbltitles, tblemailaction

WHERE tbltitles.tiID = tblusers.titleID

AND tbldept.dpID = tblusers.deptID

AND tblemailaction.uea_User = tblusers.username

AND tblemailaction.uea_Accepted = 1

</cfquery>

--------------------------------------------------

But moving on to "Updated" tickets is more tricky.. requiring me to check two values:

- Value 1) Is the uea_Updated field a 1 or 0 (1=checked meaning send Emails to this User)

- Value 2) Check to see if the ttTicketType has been checked for this User.

- If both are true (or Not Null), then add this email to the list of emails to send to..

This is the part that I'm not sure about.. because I guess I need to "loop" or "iterate"thru each User and produce the email list?

Report · Dec 13, 2012

You don't have to loop through anything. It's not the greatest relationship out there, but you can join from tblusers to tblTickets with the user name.

Report · Dec 14, 2012

Thanks for the suggestions. I figured out a query using UNION ALL as follows..

(The Unions do allow customization of a particular query/output email in case there are additional variables for that Type of ticket email) but need a way to condense it down..

Here is just a part of my Union query:

- when I feed it a Ticket Number, it checks the Ticket Type value on the ticket & returns just those Users who have that Type checked in the backend table.

-----------------------------------------------

SELECT CONCAT(tblemailaction.uea_User, "@company.com") AS User_email, tblemailaction.uea_ADSL

FROM tblemailaction, tbltickets, tbltickettype

WHERE tbltickets.ttType = tbltickettype.ttDesc

AND tbltickettype.ttID = tblemailaction.uea_ADSL

AND ttNum = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.id#">

UNION ALL

SELECT CONCAT(tblemailaction.uea_User, "@company.com") AS User_email, tblemailaction.uea_ADSL_INSTALL

FROM tblemailaction, tbltickets, tbltickettype

WHERE tbltickets.ttType = tbltickettype.ttDesc

AND tbltickettype.ttID = tblemailaction.uea_ADSL_INSTALL

AND ttNum = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.id#">

UNION ALL

SELECT CONCAT(tblemailaction.uea_User, "@company.com") AS User_email, tblemailaction.uea_Cabling

FROM tblemailaction, tbltickets, tbltickettype

WHERE tbltickets.ttType = tbltickettype.ttDesc

AND tbltickettype.ttID = tblemailaction.uea_Cabling

AND ttNum = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.id#">

UNION ALL

SELECT CONCAT(tblemailaction.uea_User, "@company.com") AS User_email, tblemailaction.uea_Calix

FROM tblemailaction, tbltickets, tbltickettype

WHERE tbltickets.ttType = tbltickettype.ttDesc

AND tbltickettype.ttID = tblemailaction.uea_Calix

AND ttNum = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.id#">