I have the following code which generates a table of data and sends out to an email, but I need it to group by the Status "new_Data" field. Here is what it currently produces: Here is what it should produce: I'm not sure how to change the grouping in my table code to get the Status (in yellow) to break properly? Here is my current code: <!--- Only send the email Mon-Fri ---> <cfset weekday = DayOfWeek(Now())> <cfif (weekday eq 1) or (weekday eq 7)> <cfabort> </cfif> <!--- Query for the Status Audit records that were Changed yesterday ---> <cfquery name="rsAudit_Status" datasource="cwo"> SELECT tblcom.omID, DATE_FORMAT(tblcom.omEstCompDate,'%a %m-%d-%Y') AS ecd, omOwner, tblcom.omContractDate, tblcom.omStatusID, tblStatus.stSort, tblcom.omType, IF(tblcom.omDelayed='N', 'Priority Builds', 'Delayed') AS delay_name, tblcom.omCustName, tblcom.omCustAddress, tblcom.omCity , omWorkUnits , omBuildClass , IFNULL(Datediff(NOW(),omDate),0) AS DOS , DATE_FORMAT(tblaudit.modified,'%m-%d-%Y') AS alm , atID , tblaudit.new_Data , tblaudit.modifiedby , DATE_FORMAT(tblaudit.modified,'%a %m-%d-%Y') AS report_label_date , "" AS DAFF_sum , "" AS DABL_sum FROM tblaudit LEFT OUTER JOIN tblcom ON tblaudit.comID = tblcom.omID LEFT OUTER JOIN tblstatus ON tblstatus.stDesc = tblaudit.new_data WHERE DATE(tblaudit.modified) = DATE(NOW() - INTERVAL 1 DAY) ORDER BY tblStatus.stSort </cfquery> <!---// create the HTML email //---> <cfmail to="mgr@domain.com" bcc="" from="xxx@domain.com" subject="Daily Summary report.." group="new_Data"> <html> <head> <title>HTML E-mail</title> </head> <body> <!--- If query returns no records ---> <cfif rsAudit_Status.RecordCount EQ 0> <cfmailpart type="text"> .. </cfmailpart> <cfmailpart type="html"> No customer Statuses were updated yesterday.. </cfmailpart> <!--- Otherwise send email with records ---> <cfelse> <cfmailpart type="text"> .. </cfmailpart> <cfmailpart type="html"> <!--- Style Tag in the Body, not Head, for Email ---> <style type="text/css"> body { font-size: 14px; } </style> <strong>Status Updates</strong> were completed yesterday for the following data customer(s).. <cfoutput> <table border="1"> <tr bgcolor="##6296C8" class="body"> <th>Status</th> <th>Customer</th> <th>City</th> <th>DABL</th> <th>DAFF</th> <th>Mod By</th> <th>TDO</th> </tr> <cfloop query="rsAudit_Status"> <tr bgcolor="###iif(currentrow MOD 2,DE('ffffff'),DE('efefef'))#"> <td>#rsAudit_Status.new_Data#</td> <td>#rsAudit_Status.omCustName#</td> <td>#rsAudit_Status.omCity#</td> <td>#rsAudit_Status.DABL_sum#</td> <td>#rsAudit_Status.DAFF_sum#</td> <td>#rsAudit_Status.modifiedby#</td> <td>#rsAudit_Status.DOS#</td> </tr> </cfloop> </table> </cfoutput> <p> <em>(Note: This report is also available from the CRM in PDF) </em><br> <p>Thanks,<br> Project Team</p> </cfmailpart> </cfif> </body> </html> </cfmail> </cfmail>
... View more