Solved

cfmail from a query, avoiding doublicats

Posted on 2004-10-20
10
274 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
  • 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
 
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
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…
This video discusses moving either the default database or any database to a new volume.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now