Solved

cfmail from a query, avoiding doublicats

Posted on 2004-10-20
10
280 Views
Last Modified: 2013-12-24
Hi,
I have the users's info. in an sql db. many of the info. it spread over different tables. One table contains comments entered by other users concerned with a the file the specific user has uploaded.

the lay out is as follows, filesTable(contains file_ID,Location,title), ContributionTable(file_ID, Type_ID, Topic_ID, User_ID), UserTable(contains users info, keyed on User_ID), commentsTable(contains File_ID, Comments, Comments to Organizers) to cut the story short every thing is connected through the "ID" numbers.
Now when more than one comment is entered the file_ID will be repeated as many time as there is a contribution. I have made inner joins to reterive many data to put in the e-mail. It all works fine, the only problem is that it will send as many e-mails as there are comments. I have made a loop inside the cfmail tag to loop over the comments, so they are all sent in one e-mail, but as i said, this e-mail is sent for times for the four comments entered.
I need to stop that. I have tried using the " group by" in my query, but that generated the problem that i had to get remove all the columns that are not included in the group by.

any ways, here is my query

<cfquery name="files" datasource="#Application.datasource#" username="#Application.Username#"
   password="#Application.Password#">
            Select UsersTabel.user_ID, UsersTabel.L_Name,UsersTabel.F_Name, UsersTabel.Title,
                        Participants.grou, Participants.department,Participants.affiliation, Participants.country,
                        UsersTabel.address, UsersTabel.city, UsersTabel.zip, UsersTabel.EMail,
                        Ab.Abst_ID, Ab.title as tit, topi.topic, absta.AcType as accepted,
                        Co.Type_ID,  Ty.Type , abRef.Comments as comment
                        
                         From dbo.UsersTabel.
            Inner join contributionTable co
            On UsersTabel.user_ID = co.P_ID
            Inner join  file ab
            On ab.abst_ID = co.abst_ID
            Inner join topics topi
            On topi.topic_ID = co.topic_ID
            Inner join  type ty
            On ty.type_ID = co.type_ID
            Inner join commentsTable abRef
            On ab.abst_ID = abRef.Abst_ID
            Inner Join abst_Status absta
            on ab.abst_ID = absta.Abst_ID
            where UsersTabel.user_ID =#Pinfo[5]#
</cfquery>

my mail tag
<cfmail       query="files"
                        server=""
                        username = ""
                        password = ""
                        to="#Email#"
                        from=""
                        bcc=""
                        replyto =""
                        subject=""
                        type = "text/html"
                        spoolenable = "Yes" >
                        Dear #Pinfo[1]# #Pinfo[4]#, #Chr(13)##chr(10)#<br />
                        #chr(10)##chr(13)#<br />
                        
                        File Title: #tit##Chr(13)##chr(10)#<br />
                        Topic: #topic##Chr(13)##chr(10)#<br />
                        Contribution requested: #type##Chr(13)##chr(10)#<br />
                        <cfloop query="abstracts">Comments from the referees: #comment##Chr(13)##chr(10)#<br /></cfloop>
                        Contribution Accepted as: #accepted##Chr(13)##chr(10)#<br />
      
                        Please Keep this e-mail for your records.#Chr(13)##chr(10)#<br />
                        #Chr(13)##chr(10)#<br />
                        If you have any questions regarding your file please contact us at:
                  </cfmail>
any ideas
thanks
0
Comment
Question by:MMsabry
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 5

Expert Comment

by:kkhipple
ID: 12357118
why dont you perform the loop before the CFMAIL tag .. and have all the comments appended to an empty string

<CFSET comments1 = "">

<CFLOOP QUERY="abstracts">
   <CFSET comments1 = comments1 + #comment#>
</CFLOOP>

and then in your CFMAIL tag, just print the comments

... Comments from the referees: #comments1# ...
0
 
LVL 5

Expert Comment

by:elvistheprince
ID: 12357166
Have you tried something like this outside the CFMAIL:
<cfset comments ="">
<cfloop query="abstracts">
 <cfset comments =#comments#&"Comments from the referees: "&#comment#&#Chr(13)#&#chr(10)#&"<br />">
</cfloop>

Then in the CFMAIL just  use #comments#

Stephen
0
 
LVL 5

Expert Comment

by:elvistheprince
ID: 12357172
Damn pipped to the post!, Grr last time I check my solution before posting :-)
0
Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

 
LVL 17

Accepted Solution

by:
anandkp earned 500 total points
ID: 12360475
try this

<CFMAIL Query="files" Group="#eMail#" ..... other attributes for CFAMIL here .... >
       Dear #Pinfo[1]# #Pinfo[4]#, #Chr(13)##chr(10)#<br />
                    #chr(10)##chr(13)#<br />
                   
                    File Title: #tit##Chr(13)##chr(10)#<br />
                    Topic: #topic##Chr(13)##chr(10)#<br />
                    Contribution requested: #type##Chr(13)##chr(10)#<br />
                    Comments from the referees: <CFOUTPUT>#comment#<BR><BR></CFOUTPUT>
                    Contribution Accepted as: #accepted##Chr(13)##chr(10)#<br />
     
                    Please Keep this e-mail for your records.#Chr(13)##chr(10)#<br />
                    #Chr(13)##chr(10)#<br />
                    If you have any questions regarding your file please contact us at:
</CFMAIL>

use the colume that uniquely identifies a email with all the comments - i think the #email# shld do it - but u surely can change it - if u have a unique column to identify this ...
0
 

Author Comment

by:MMsabry
ID: 12369439
Anandkp,
 thanks that worked, only one e-mail is sent. Now my problem is that this way it only out puts one record, so I changed the code to
Dear #Pinfo[1]# #Pinfo[4]#, #Chr(13)##chr(10)#<br />
                        #chr(10)##chr(13)#<br />
                        This is an automatic reply to announce your file Acceptance.#Chr(13)##chr(10)#<br />
                        <cfoutput>
                        file Title: #tit##Chr(13)##chr(10)#<br />
                        Topic: #topic##Chr(13)##chr(10)#<br />
                        Contribution requested: #type##Chr(13)##chr(10)#<br />
                        <cfoutput>Comments from the referees: #comment##Chr(13)##chr(10)#<br /></cfoutput>
                        Contribution Accepted as: #accepted##Chr(13)##chr(10)#<br />
                        #Chr(13)##chr(10)#<br />
                        </cfoutput>
                        
                        Please Keep this e-mail for your records.#Chr(13)##chr(10)#<br />
                        #Chr(13)##chr(10)#<br />
                        If you have any questions regarding your file please contact us at: #Chr(13)##chr(10)#<br />
                        If you have any questions regarding the registration procedure please contact us at: #Chr(13)##chr(10)#<br />
                        #Chr(13)##chr(10)#<br />
                        #Chr(13)##chr(10)#<br />
                        Thank you for submitting your file.#Chr(13)##chr(10)#<br />
Now it works and displayes all records related to that person.
The only things is that it will repeat everything as many times as there are records. I want it to repeat only the comments and not the full thing, should i put th group on title also in the cfoutput tag?
thanks
0
 
LVL 5

Expert Comment

by:kkhipple
ID: 12369545
i figured you had wanted to loop thru your comments?
Isn't what you wanted. So that all the comments are displayed.
0
 

Author Comment

by:MMsabry
ID: 12369584
Yes, all the comments are displayed, but not the title, accepted as, requested as, etc
now it loops over the whole thing
0
 
LVL 5

Expert Comment

by:kkhipple
ID: 12369598
could it be possible if we could see the output?

im not sure ... but maybe, im lost?  :S    
0
 

Author Comment

by:MMsabry
ID: 12369654
Here is an example out put, two enteries for this file, but the referees only grades and did not put comments. As you can see the whole thing is repeated twice. If i remove the output tag, and there are two different files, (not one with two enteries) I only see one. That is why I used the output tag.
 
This is an automatic reply to announce your abstract Acceptance.
Abstract Title: Photoelectrochemical Response Enhancement of Poly(terthiophenes) by Using Nanoparticulated CdS
Topic: Nanomaterials
Contribution requested: Poster
Comments from the referees:
Comments from the referees:
Contribution Accepted as: Poster

Abstract Title: Photoelectrochemical Response Enhancement of Poly(terthiophenes) by Using Nanoparticulated CdS
Topic: Nanomaterials
Contribution requested: Poster
Comments from the referees:
Contribution Accepted as: Poster
0
 
LVL 17

Assisted Solution

by:anandkp
anandkp earned 500 total points
ID: 12383174
yes group that tooo ...
0

Featured Post

Turn your laptop into a mobile console!

The CV211 Laptop USB Console Adapter provides a direct Laptop-to-Computer connection for fast and easy remote desktop access with no software to install.

Question has a verified solution.

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

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

717 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