Solved

cfmail from a query, avoiding doublicats

Posted on 2004-10-20
10
279 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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

Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

Question has a verified solution.

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

When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

733 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