Solved

cfmail from a query, avoiding doublicats

Posted on 2004-10-20
10
277 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
OpenLDAP set password to expire 7 697
website file permissions 4 77
Asp.net plan security, reliable, stable 2 126
How to move and access a folder outside of the root folder 4 95
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

839 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