Help using a Grouped Table inside cfmail?

Contributor ,
Dec 07, 2017 Dec 07, 2017

Copy link to clipboard

Copied

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:
  • table1.GIF
  • Here is what it should produce:
  • table2.GIF

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>

Views

538

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct Answer

LEGEND , Dec 07, 2017 Dec 07, 2017
You need to add type="html" to your CFMAIL tag.HTH,^ _ ^

Likes

Translate

Translate
Contributor ,
Dec 07, 2017 Dec 07, 2017

Copy link to clipboard

Copied

Looks like my code did not post? Here it is again:

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 07, 2017 Dec 07, 2017

Copy link to clipboard

Copied

Use the group="column" attribute in nested CFOUTPUT tags (the innermost CFOUTPUT cannot have this attribute set; it must be left out.)  I do not think CFLOOP has this attribute available, so switch to CFOUTPUT.

      <cfoutput query="rsAudit_Status" group="new_Data">

     <tr bgcolor="##???"><td colspan="6">#rsAudit_Status.new_Data#</td></tr>

       <cfoutput>
      <tr bgcolor="###iif(currentrow MOD 2,DE('ffffff'),DE('efefef'))#">
        <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>
      </cfoutput>
      </cfoutput>

HTH,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Dec 07, 2017 Dec 07, 2017

Copy link to clipboard

Copied

HTH, thanks for the reply!

I tried your suggestion but get the following error:

Error Occurred While Processing Request

Invalid tag nesting configuration.

A query driven cfoutput tag is nested inside a cfoutput tag that also has a query attribute. This is not allowed. Nesting these tags implies that you want to use grouped processing. However, only the top-level tag can specify the query that drives the processing.
The error occurred in C:\inetpub\wwwroot\xxx\xxx\reports\Email_Reports_Status_Change_Summary.cfm: line 64
62 :     <table border="1"> 63 : 64 :       <cfoutput query="rsAudit_Status" group="new_Data">   65 :      <tr bgcolor="##6296C8"><td colspan="6">#rsAudit_Status.new_Data#</td></tr> 66 :        <cfoutput>  

Here is my updated code:

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

   

    <table border="1">

     

      <cfoutput query="rsAudit_Status" group="new_Data"> 

     <tr bgcolor="##6296C8"><td colspan="6">#rsAudit_Status.new_Data#</td></tr> 

       <cfoutput> 

      <tr bgcolor="###iif(currentrow MOD 2,DE('ffffff'),DE('efefef'))#"> 

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

      </cfoutput> 

      </cfoutput> 

    </table>

  

    <p>

    

      <em>(Note: This report is also available from the CRM in PDF) </em><br>

           

    <p>Thanks,<br>

    Clearwave Project Team</p>

  </cfmailpart>

</cfif>

</body>

</html>

</cfmail>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 07, 2017 Dec 07, 2017

Copy link to clipboard

Copied

That's very strange, seeing as how the error message clearly shows the code, and the nested CFOUTPUT does not have the query attribute set.  What version of CF are you using?  Does it have all of the latest updates?

(BTW.. if you use the advanced editor, there is a button that looks like two 'greater than' symbols (>>) that will allow you to encapsulate code to make it more readable.  Just highlight your code, click the button, choose "Syntax Highlighting" and then XML, Java, SQL, Plain, etc.)

<cfoutput>#cgi.server_name#</cfoutput>

(BTW2.. HTH = Hope This Helps, my signature is ^ _ ^, an attempt to look like the head of a wolf, my alias being WolfShade.  )

V/r,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 07, 2017 Dec 07, 2017

Copy link to clipboard

Copied

D'OH!  Is your code already surrounded by CFOUTPUT tags???  That would trigger that kind of message.

HTH,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 07, 2017 Dec 07, 2017

Copy link to clipboard

Copied

Good news.  Adobe must have added the group attribute to CFLOOP, recently, because according to this, you can use group attribute.

So, change the CFOUTPUT for the mail to CFLOOP, and it should work just fine.

HTH,


^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Dec 07, 2017 Dec 07, 2017

Copy link to clipboard

Copied

My CF version is 9.0 so it doesn't support the CFLOOP grouping..

I also tried using cfsavecontent, which delivers the email fine, but the body just shows up in raw <table> <tr> html.. no real data?

<cfsavecontent variable="mailContent">

    <strong>Status Updates</strong> were completed yesterday for the following data customer(s)..

    <cfoutput query="rsAudit_Status" group="new_Data"> 

<table border="1">

      <tr>

        <th>Customer</th>

        <th>City</th>

        <th>DABL</th>

        <th>DAFF</th>

        <th>Mod By</th>

        <th>TDO</th>

      </tr>

     

    

     <tr bgcolor="##FBF2D7"><td colspan="6" bgcolor="##FBF2D7"><strong>#rsAudit_Status.new_Data#</strong></td></tr> 

       <cfoutput> 

      <tr bgcolor="###iif(currentrow MOD 2,DE('ffffff'),DE('efefef'))#"> 

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

      </cfoutput> 

      </cfoutput> 

    </table>

  

    <p>

     

      <em>(Note: This report is also available from the CRM in PDF) </em><br>

    <p>Thanks,<br>

    Clearwave Project Team</p>

</cfsavecontent>

<cfmail to="user@domain.com" from="sys@domain.com" subject="Daily Summary report..">

    #mailContent#

</cfmail>

</body>

</html>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Dec 07, 2017 Dec 07, 2017

Copy link to clipboard

Copied

<cfsavecontent variable="mailContent">

    <strong>Status Updates</strong> were completed yesterday for the following data customer(s)..

    <cfoutput query="rsAudit_Status" group="new_Data"> 

<table border="1">

      <tr>

        <th>Customer</th>

        <th>City</th>

        <th>DABL</th>

        <th>DAFF</th>

        <th>Mod By</th>

        <th>TDO</th>

      </tr>

     

    

     <tr bgcolor="##FBF2D7"><td colspan="6" bgcolor="##FBF2D7"><strong>#rsAudit_Status.new_Data#</strong></td></tr> 

       <cfoutput> 

      <tr bgcolor="###iif(currentrow MOD 2,DE('ffffff'),DE('efefef'))#"> 

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

      </cfoutput> 

      </cfoutput> 

    </table>

  

    <p>

     

      <em>(Note: This report is also available from the CRM in PDF) </em><br>

    

     

    <p>Thanks,<br>

    Clearwave Project Team</p>

</cfsavecontent>

<cfmail to="user@domain.com" from="domain.com" subject="Daily Summary report..">

    #mailContent#

</cfmail>

</body>

</html>

I tried using the advanced editor, but when I save the post, it leaves off my code?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 07, 2017 Dec 07, 2017

Copy link to clipboard

Copied

jlig  wrote

I tried using the advanced editor, but when I save the post, it leaves off my code?

I see your code fine. 

V/r,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 07, 2017 Dec 07, 2017

Copy link to clipboard

Copied

Thank you for marking my answer as correct.  I do appreciate it.

V/r,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 07, 2017 Dec 07, 2017

Copy link to clipboard

Copied

You need to add type="html" to your CFMAIL tag.

HTH,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Dec 07, 2017 Dec 07, 2017

Copy link to clipboard

Copied

WolfShade, that was the fix to make the cfsavecontent version of my page work!

Here is the final working code.. Thanks for your help!

<!--- 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 body, that includes grouping, then save to variable //--->

<cfsavecontent variable="mailContent">

<!---Begin data looping--->

<strong>Status Updates</strong> were completed yesterday for the following data customer(s)..

   

<table border="1">

      <tr bgcolor="#6296C8">

        <th bgcolor="#6296C8">Customer</th>

        <th bgcolor="#6296C8">City</th>

        <th bgcolor="#6296C8">DABL</th>

        <th bgcolor="#6296C8">DAFF</th>

        <th bgcolor="#6296C8">Mod By</th>

        <th bgcolor="#6296C8">TDO</th>

      </tr>

      <cfoutput query="rsAudit_Status" group="new_Data"> 

    

     <tr bgcolor="##FBF2D7"><td colspan="6" bgcolor="##FBF2D7"><strong>#rsAudit_Status.new_Data#</strong></td></tr> 

       <cfoutput> 

      <tr> 

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

      </cfoutput> 

      </cfoutput> 

    </table>

  

    <p>

     

      <em>(Note: This report is also available from the CRM in PDF) </em><br>

           

    <p>Thanks,<br>

   Project Team</p>

</cfsavecontent>

<html>

<head>

    <title>HTML E-mail</title>

</head>

<body>

<!--- Send Email using cfmail, including previous cfsavecontent variable --->

<cfmail to="user@domain.com" from="mgt@domain.com" subject="Daily Summary report.." type="html" >

    #mailContent#

</cfmail>

</body>

</html>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Dec 07, 2017 Dec 07, 2017

Copy link to clipboard

Copied

I'm really surprised to hear you say that this "works", jlig. The GROUP attribute (of CFOUTPUT, CFLOOP, or CFMAIL--yes, it has one) is supposed to name the column in a query by which you SORTED it.

You sorted your query by a column called stsort, but you are grouping the query by "new_data" (which we see now is in fact the column you are outputting in your first example as "status").

(As a point of clarification, some people misconstrue how the GROUP attribute works, and they think that they must have their SQL statement do a GROUP BY, but in fact they serve different and actually opposite purposes: the GROUP BY would have SQL return only the aggregation of records by that column, where as the ORDER BY and then CF's GROUP attribute causes CF to "group the display" of records by that sorted column.)

So I'm just saying beware, jlig: your output may seem to be "working" (in that something is showing up), and it may even seem to be "grouping" (there are different records under each status), but I fear it's not doing what you REALLY want. Instead, I would think you'd want to have your query do an:

order by new_data

to get things really "grouped by" that column, or perhaps "order by new_data, stsort", if somehow the stsort is of secondary importance to the primary sorting/grouping by status.

Or might it be that stsort somehow coincidentally happens to have the same data as new_data? We can't know that from the info above. That would be the only way that the result here would be "right".

Curious to hear what you may have to say. Sorry if somehow I seem to be misunderstanding something. Hope it's helpful.


/Charlie (server troubleshooter, carehart.org)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Dec 07, 2017 Dec 07, 2017

Copy link to clipboard

Copied

LATEST

Charlie, as always I appreciate your insight!

That "new_Data" field is linked to the status table, and the sort field.

This sort value is unique for each status, which means it's basically "uniquely" synonymous with the "new_Data" value.

om_sql2.GIF

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines