• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 94
  • Last Modified:

CF - SQL sending email from query problem

I have a program where it calculates the monthly bonus for employees in each dept for the previous month.  It is then sent in a email to payroll.  It works fine except it sends an email for each employee or record - they want all the employees from one dept in one email - listing the employees instead of one email for each.  Is there a way to get all the records to send in the same email?


Query
SELECT     monthlybonus.bonus_amt AS TotalBonus, monthlybonus.paybonus, monthlybonus.has_sig1, monthlybonus.what_depts, employee_data.email_name, 
                      monthlybonus.monthdate, employee_data.first_name, employee_data.last_name, monthlybonus.employeeID, employee_data.ID, monthlybonus.whosthesig1, 
                      employee_data_1.ID AS Expr1, employee_data_1.email_name AS LCemail_name
FROM         monthlybonus INNER JOIN
                      employee_data ON monthlybonus.employeeID = employee_data.ID INNER JOIN
                      employee_data AS employee_data_1 ON monthlybonus.whosthesig1 = employee_data_1.ID
WHERE     (monthlybonus.whosthesig1 = '#sessionID#') AND (monthlybonus.has_sig1 = 1) AND (monthlybonus.paybonus = 1) AND 
                      (monthlybonus.monthdate = CASE WHEN month(getDate()) = 12 THEN 1 ELSE month(getDate()) - 1 END)

Open in new window


Email form
<CFMAIL query="getSubmitted"
            to="email@company.com"
            from="#getSubmitted.LCemail_name#@company.com"
            spoolenable="no"
            type="html"
            Subject="TEAM PERFORMANCE AWARDS EMAIL">
    <table border="0" cellpadding="0" cellspacing="0" style="width:100%; font-family:Arial, Helvetica, sans-serif;">
      <tr>
        <td><p>Dear Payroll,
          <p>Submission :#DateFormat(Now(), "mmm-dd-yyyy")#
          <p>BONUS AMOUNT = #NumberFormat("#TotalBonus#",'$____.__')# - #what_depts# for the month of #DateFormat(DateAdd('m', -1, MyDateTime),'mmmm')# 
          For #first_name# 	#last_name#<br>

          <p></td>
      </tr>
    </table>
  </CFMAIL>

Open in new window

0
JohnMac328
Asked:
JohnMac328
  • 3
  • 3
2 Solutions
 
_agx_Commented:
> <CFMAIL query="getSubmitted"

By default that generates one email per query record.  Try adding the "group" attribute. It should work like <cfoutput query="..." group="...">

Be sure to sort the query results by DEPT first

     
      SELECT ....
      WHERE     (monthlybonus.whosthesig1 = '#sessionID#') ....
      ORDER BY monthlybonus.what_depts    <=== sort by department FIRST


Then "group" by the department column. Inside the email tag, use cfoutput tags to display the individual employees.  Something like this.  

         <CFMAIL to="useATestEmailOnly@company.com" query="getSubmitted" group="what_depts" ....>

           <!--- this should only print once per dept --->
           Dear Payroll,
          <p>Submission :#DateFormat(Now(), "mmm-dd-yyyy")#

          <!--- this should print all employee records within the current department --->    
          <cfoutput>
    <p>BONUS AMOUNT = #NumberFormat("#TotalBonus#",'$____.__')# - #what_depts# for the month of #DateFormat(DateAdd('m', -1, MyDateTime),'mmmm')# 
          For #first_name# 	#last_name#<br>
           </cfoutput>

        </CFMAIL>

Open in new window

0
 
JohnMac328Author Commented:
Hi agx,

I added the grouping and the order by whats_depts in the query but only one email with one record goes out.

       Subject="TEAM PERFORMANCE AWARDS EMAIL"
            group="what_depts"
0
 
JohnMac328Author Commented:
I threw in a cfloop to see what would happen and it worked!

 <cfloop query="getSubmitted">

    <table border="0" cellpadding="0" cellspacing="0" style="width:100%; font-family:Arial, Helvetica, sans-serif;">
      <tr>
        <td><p>Dear Payroll,
          <p>Submission :#DateFormat(Now(), "mmm-dd-yyyy")#
          <p>BONUS AMOUNT = #NumberFormat("#TotalBonus#",'$____.__')# - #what_depts# for the month of #DateFormat(DateAdd('m', -1, MyDateTime),'mmmm')#
          For #first_name#       #last_name#<br>

          <p></td>
      </tr>
    </table>
    </cfloop>
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
_agx_Commented:
Sorry, I got called away.


>> only one email with one record goes out.

I'm guessing you forgot to add the extra <cfoutput> tags inside the cfmail tag :)


 <CFMAIL to="useATestEmailOnly@company.com" query="getSubmitted" group="what_depts" ....>

           <!--- this should only print once per dept --->
           Dear Payroll,
          <p>Submission :#DateFormat(Now(), "mmm-dd-yyyy")#

          <!--- this should print all employee records within the current department --->    
          <cfoutput>
    <p>BONUS AMOUNT = #NumberFormat("#TotalBonus#",'$____.__')# - #what_depts# for the month of #DateFormat(DateAdd('m', -1, MyDateTime),'mmmm')#
          For #first_name#       #last_name#<br>
           </cfoutput>

        </CFMAIL>
0
 
_agx_Commented:
EDIT:

I threw in a cfloop to see what would happen and it worked!

Having said that, personally, I'd go with what you came up with instead. I think cfloop is cleaner :)  (I was going to suggest a loop, but was not sure if you wanted one email per dept - or one overall.)

Don't forget to mark your comment as the answer to close out the thread.
0
 
JohnMac328Author Commented:
I liked your tip also :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now