[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

cfmail from a query, avoiding doublicats

Posted on 2004-10-20
10
Medium Priority
?
282 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
Understanding Web Applications

Without even knowing it, most of us are using web applications on a daily basis. Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We often confuse these web applications tools for websites.  So, what is the difference?

 
LVL 17

Accepted Solution

by:
anandkp earned 2000 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 2000 total points
ID: 12383174
yes group that tooo ...
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

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 …
In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

656 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