asked on

Generating a proper cfloop for cfmail

I have a QofQ that I am taking a distinct listing of assigned people that I want to loop through and send emails to them for their specific issues.

currently, the cfmail works but it sends each record as an email instead of placing all the issues for one person in one email, ---send, and then do the next person --send them their issues,,,,,,,

For example. I have a total of 20 records with a total of 12 assigned people. So, I want 12 emails(with issues combined for the same person under one email) not 20 seperate emails. I am attaching code.
****
Major problem: I am rushed.

***

Here is code:

<cfset session.assignee = #ValueList(qCaWebAssigneList.assignee)#>
<cfoutput>#session.assignee#</cfoutput>

<cfloop From = "1" To = "#ListLen(session.assignee)#" index = "Counter">

 <cfquery name="qActionDetail" dbtype="query">
 Select *
 from qCaweb
 where assignee_psid = '#ListGetAt(session.assignee, Counter)#'
 </cfquery>

 <cfdump var="#qActionDetail#">

<cfmail to="Man@cupid.com"
 cc="Man@cupid.com"
 bcc="Man@cupid.com"
 from="Man@cupid.com"
 query="qActionDetail"
 subject="CaWeb Actions Coming Due - Wkly Automated Notice" type="html">

 <h3>SUPPLY CHAIN Open CaWeb Actions as of Week Ending #DateFormat(Now(),'MM/DD/YYYY')#</h3>
 
<table>
 <tr>
 <th nowrap> Assignee </th>
 <th nowrap> Action No. </th>
 <th nowrap> database </th>
 <th nowrap> Date Opened </th>
 <th nowrap> Date Due </th>
 <th nowrap> Days Til Due </th>
 <th nowrap> Age Days </th>
 <th nowrap>Originator </th>
 </tr>
 </table>

 <table>

 <tr <cfif #currentrow# mod 2> bgcolor="eaeaea" </cfif>>
 <td nowrap> #assignee_psid# </td>
 <td nowrap> #cawebnumber# </td>
 <td nowrap><cfif databasename is "apc">Advanced Product Center (Texas)
 <cfelseif databasename is "atf">ATFLIR
 <cfelseif databasename is "cam">California Main
 <cfelseif databasename is "c2k">CIP 2K
 <cfelseif databasename is "esw">ES Wide Site-to-Site
 <cfelseif databasename is "f18">FA-18/AESA
 <cfelseif databasename is "f22">F22 PICC2
 <cfelseif databasename is "fst">Forest
 <cfelseif databasename is "gew">Goleta EW
 <cfelseif databasename is "jsf">JSF ICP
 <cfelseif databasename is "p96">P1196
 <cfelseif databasename is "pas">PASS
 <cfelseif databasename is "sts">STSS
 </cfif>
 </td>

 <td nowrap> #dateopened# </td>
 <td nowrap> #datedue# </td>


 <cfif daystildue gt 5>
 <td nowrap bgcolor="Green"> #daystildue# </td>
 <cfelseif daystildue ge 0 and daystildue le 5>
 <td nowrap bgcolor="Yellow"> #daystildue# </td>
 <cfelseif daystildue lt 0>
 <td nowrap bgcolor="Red"> #daystildue# </td>
 </cfif>

 <cfif agedays lt 14>
 <td nowrap bgcolor="Green"> #agedays# </td>
 <cfelseif agedays ge 14 and agedays le 30>
 <td nowrap bgcolor="Yellow"> #agedays# </td>
 <cfelseif agedays gt 30>
 <td nowrap bgcolor="Red"> #agedays# </td>
 </cfif>
 <td nowrap>#initiator# </td>
 </tr>
 </table>
</cfmail>
</cfloop>

_agx_

> I have a total of 20 records with a total of 12 assigned people.

I haven't tried it, but what about grabbing the 20 records in one query, then using cfoutput's "group"

<cfoutput query="yourQuery" group="theAssigneeColumn">
<cfmail ...>
<cfoutput>
#theDetailQueryFieldsHere#
</cfoutput>
</cfmail>
</cfoutput>

mahpog

ASKER

I have tried <cfoutput query="yourQuery" group="theAssigneeColumn">

It still outputs one record at a time. So, instead of sending someone with 3 issues in one email, I get 1 issue per email.( so 3 emails to the same person for 3 issues, and not 1 email with the 3 issues listed).

I took out the cfmail and just output online to table and i get the 3 messages combined for the one person.

_agx_

That could happen if you forget to remove query="qActionDetail" from the <cfmail> tag.

<cfoutput query="yourQuery" group="theAssigneeColumn">

<cfmail ...>
<cfoutput>
#theDetailQueryFieldsHere#
</cfoutput>
</cfmail>
</cfoutput>
back to top

mahpog

ASKER

I did that also. I did figure out the solution. I am pasting..... I did an additional cfloop and it worked like a charm.
<cfloop From = "1" To = "#ListLen(session.assignee)#" index = "Counter">

<cfmail to="Man@cupid.com"
 cc="Man@cupid.com"
 bcc="Man@cupid.com"
 from="Man@cupid.com"
 subject="Web Actions Coming Due - Wkly Automated Notice" type="html">

 <h3>Actions as of Week Ending #DateFormat(Now(),'MM/DD/YYYY')#</h3>
 
<table>
 <tr>
 <th nowrap> Assignee </th>
 <th nowrap> Action No. </th>
 <th nowrap> database </th>
 <th nowrap> Date Opened </th>
 <th nowrap> Date Due </th>
 <th nowrap> Days Til Due </th>
 <th nowrap> Age Days </th>
 <th nowrap>Originator </th>
 </tr>
 </table>

 <cfquery name="qActionDetail" dbtype="query">
 Select *
 from web
 where assignee_psid = '#ListGetAt(session.assignee, Counter)#'
 </cfquery>

 <table>
 <cfloop query="qActionDetail" startrow="1" endrow="#qActionDetail.recordcount#">
 <tr <cfif #qActionDetail.currentrow# mod 2> bgcolor="eaeaea" </cfif>>
 <td nowrap> #qActionDetail.assignee_psid# </td>
 <td nowrap> #qActionDetail.cawebnumber# </td>
 <td nowrap><cfif qActionDetail.databasename is "apc">Advanced Product Center (Texas)
 <cfelseif qActionDetail.databasename is "atf">ATFLIR
 <cfelseif qActionDetail.databasename is "cam">California Main
 <cfelseif qActionDetail.databasename is "c2k">CIP 2K
 <cfelseif qActionDetail.databasename is "esw">ES Wide Site-to-Site
 <cfelseif qActionDetail.databasename is "f18">FA-18/AESA
 <cfelseif qActionDetail.databasename is "f22">F22 PICC2
 <cfelseif qActionDetail.databasename is "fst">Forest
 <cfelseif qActionDetail.databasename is "gew">Goleta EW
 <cfelseif qActionDetail.databasename is "jsf">JSF ICP
 <cfelseif qActionDetail.databasename is "p96">P1196
 <cfelseif qActionDetail.databasename is "pas">PASS
 <cfelseif qActionDetail.databasename is "sts">STSS
 </cfif>
 </td>
</tr>
</table>
</cfloop>
</cfmail>
</cfloop>

***
Problem fixed.

_agx_

Yes, that's exactly what the cfoutput with "group" is supposed to do.

Out of curiosity, can you post the cfoutput code you tried that didn't work?

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

mahpog

ASKER

agx, I do not have the 'old" code anymore. Once I got the result I wanted, I deleted the old code. I did code as you suggested minus using a param as you did, and I did not get the result. I also thought I should get the same result as you stated in your last note. I will give points just for the fact that you took the time to look at this for me. thx, mahpog. And yes, I am pressed for time right now.

_agx_

mahpog,

I was mostly curious about whether the concept would work, but thanks for the points.

I tested the code with a "manual" query, but realized two things are missing from the untested cfquery code that may account for the difference.

For PAQ purposes, when passing in a list of values to cfqueryparam, the "list" attribute is required. Also when using "group" the results _must_ be ordered by the "grouped" field(s) first. You can also order by other fields if desired, but the "grouped" field(s) must be first.

<cfquery name="qActionDetail" dbtype="query">
Select *
from qCaweb
where assignee_psid IN
(
<cfqueryparam value="#session.assignee#' cfsqltype="cf_sql_varchar" list="true">
)
ORDER BY assignee_psid
</cfquery>