[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Generating a proper cfloop for cfmail

Posted on 2007-10-03
8
Medium Priority
?
293 Views
Last Modified: 2013-12-16
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>       
0
Comment
Question by:mahpog
  • 5
  • 3
8 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 20010631
> 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
 

Author Comment

by:mahpog
ID: 20023909
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
 
LVL 52

Expert Comment

by:_agx_
ID: 20023983
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 

Author Comment

by:mahpog
ID: 20025288
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
 
LVL 52

Expert Comment

by:_agx_
ID: 20025308
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
 
LVL 52

Accepted Solution

by:
_agx_ earned 1500 total points
ID: 20028005
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
 

Author Comment

by:mahpog
ID: 20030760
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
 
LVL 52

Expert Comment

by:_agx_
ID: 20030943
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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
The purpose of this video is to demonstrate how to connect a WordPress website to Google Analytics. This will be demonstrated using a Windows 8 PC Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php :…
The purpose of this video is to demonstrate how to create a Printer Friendly PDF on a WordPress Page. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome Screenshot” Google Chrome Extension, and SmallPDF.com Log…
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question