asked on

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

_agx_

> <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

JohnMac328

ASKER

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"

ASKER CERTIFIED SOLUTION

JohnMac328

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

_agx_

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.

JohnMac328

ASKER

I liked your tip also :)