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><font face="Arial" color="Red">SUPPLY CHAIN Open CaWeb Actions as of Week Ending #DateFormat(Now(),'MM/DD/YYYY')#</font></h3>
            <br>
        <table>
              <tr>
                    <th nowrap><font color="navy" size="-2">&nbsp;Assignee&nbsp;</font></th>
                  <th nowrap><font color="navy" size="-2">&nbsp;Action No.&nbsp;</font></th>
                  <th nowrap><font color="navy" size="-2">&nbsp;database&nbsp;</font></th>
                  <th nowrap><font color="navy" size="-2">&nbsp;Date Opened&nbsp;</font></th>
                  <th nowrap><font color="navy" size="-2">&nbsp;Date Due&nbsp;</font></th>
                  <th nowrap><font color="navy" size="-2">&nbsp;Days Til Due&nbsp;</font></th>
                  <th nowrap><font color="navy" size="-2">&nbsp;Age Days&nbsp;</font></th>
                  <th nowrap><font color="navy" size="-2">Originator&nbsp;</font></th>
                  </tr>
            </table>

            <table>

              <tr <cfif #currentrow# mod 2> bgcolor="eaeaea" </cfif>>
                  <td nowrap><font size="-2">&nbsp;#assignee_psid#&nbsp;</font></td>
                  <td nowrap><font size="-2">&nbsp;#cawebnumber#&nbsp;</font></td>
                  <td nowrap><font size="-2"><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></font>
                  </td>
                  
                  <td nowrap><font size="-2">&nbsp;#dateopened#&nbsp;</font></td>
                  <td nowrap><font size="-2">&nbsp;#datedue#&nbsp;</font></td>

            
                  <cfif daystildue gt 5>
                        <td nowrap bgcolor="Green"><font size="-2" color="White">&nbsp;#daystildue#&nbsp;</font></td>
                  <cfelseif daystildue ge 0 and daystildue le 5>
                        <td nowrap bgcolor="Yellow"><font size="-2">&nbsp;#daystildue#&nbsp;</font></td>
                  <cfelseif daystildue lt 0>
                        <td nowrap bgcolor="Red"><font size="-2" color="White">&nbsp;#daystildue#&nbsp;</font></td>
                  </cfif>
                  
                  <cfif agedays lt 14>
                        <td nowrap bgcolor="Green"><font size="-2" color="White">&nbsp;#agedays#&nbsp;</font></td>
                  <cfelseif agedays ge 14 and agedays le 30>
                        <td nowrap bgcolor="Yellow"><font size="-2">&nbsp;#agedays#&nbsp;</font></td>
                  <cfelseif agedays gt 30>
                        <td nowrap bgcolor="Red"><font size="-2" color="White">&nbsp;#agedays#&nbsp;</font></td>
                  </cfif>
               <td nowrap><font size="-2">#initiator#&nbsp;</font></td>
            </tr>
            </table>
  </cfmail>
 </cfloop>       
mahpogAsked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
mahpog,

Yes, I think you overlooked something as you were pressed for time.  I tried using cfoutput and it works fine.

> what about grabbing the 20 records in one query, then using cfoutput's "group"

As I mentioned, your query should grab the records for all of the assignees.  ie all of the 20 records. Note, this query is untested as I don't know your data types

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

<cfdump var="#qActionDetail#">      

Then you simply use <cfoutput> and "group" on the assignee column

<cfoutput query="qActionDetail" group="Assignee_psid">
           <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><font face="Arial" color="Red">Actions as of Week Ending #DateFormat(Now(),'MM/DD/YYYY')#</font></h3><br>
                <table>
            <tr>
                 <th nowrap><font color="navy" size="-2">&nbsp;Assignee&nbsp;</font></th>
                  <th nowrap><font color="navy" size="-2">&nbsp;Action No.&nbsp;</font></th>
                  <th nowrap><font color="navy" size="-2">&nbsp;database&nbsp;</font></th>
                          <!---
                  <th nowrap><font color="navy" size="-2">&nbsp;Date Opened&nbsp;</font></th>
                  <th nowrap><font color="navy" size="-2">&nbsp;Date Due&nbsp;</font></th>
                  <th nowrap><font color="navy" size="-2">&nbsp;Days Til Due&nbsp;</font></th>
                  <th nowrap><font color="navy" size="-2">&nbsp;Age Days&nbsp;</font></th>
                  <th nowrap><font color="navy" size="-2">Originator&nbsp;</font></th>
                          --->
            </tr>
            <cfoutput>
              <tr <cfif currentrow mod 2> bgcolor="eaeaea" </cfif>>
                  <td nowrap><font size="-2">&nbsp;#qActionDetail.assignee_psid#&nbsp;</font></td>
                  <td nowrap><font size="-2">&nbsp;#qActionDetail.cawebnumber#&nbsp;</font></td>
                  <td nowrap><font size="-2"><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></font>
                  </td>
                  </tr>
                  </cfoutput>
            </table>
      </cfmail>
      <hr>
</cfoutput>


0
 
_agx_Commented:
> 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>
0
 
mahpogAuthor Commented:
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.

0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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

<cfoutput query="yourQuery" group="theAssigneeColumn">
    <!--- do not use the "query" attribute --->
     <cfmail ...>
            <cfoutput>
                #theDetailQueryFieldsHere#
            </cfoutput>
     </cfmail>
</cfoutput>
back to top
0
 
mahpogAuthor Commented:
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><font face="Arial" color="Red">Actions as of Week Ending #DateFormat(Now(),'MM/DD/YYYY')#</font></h3>
            <br>
    <table>
              <tr>
                    <th nowrap><font color="navy" size="-2">&nbsp;Assignee&nbsp;</font></th>
                  <th nowrap><font color="navy" size="-2">&nbsp;Action No.&nbsp;</font></th>
                  <th nowrap><font color="navy" size="-2">&nbsp;database&nbsp;</font></th>
                  <th nowrap><font color="navy" size="-2">&nbsp;Date Opened&nbsp;</font></th>
                  <th nowrap><font color="navy" size="-2">&nbsp;Date Due&nbsp;</font></th>
                  <th nowrap><font color="navy" size="-2">&nbsp;Days Til Due&nbsp;</font></th>
                  <th nowrap><font color="navy" size="-2">&nbsp;Age Days&nbsp;</font></th>
                  <th nowrap><font color="navy" size="-2">Originator&nbsp;</font></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><font size="-2">&nbsp;#qActionDetail.assignee_psid#&nbsp;</font></td>
                  <td nowrap><font size="-2">&nbsp;#qActionDetail.cawebnumber#&nbsp;</font></td>
                  <td nowrap><font size="-2"><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></font>
                  </td>
</tr>
</table>
</cfloop>
</cfmail>
</cfloop>

***
Problem fixed.
                  
0
 
_agx_Commented:
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?
0
 
mahpogAuthor Commented:
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.
0
 
_agx_Commented:
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>

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.

All Courses

From novice to tech pro — start learning today.